Date: Wed, 15 Jul 2020 12:48:05 -0400 (EDT) Message-ID: <846429562.2954.1594831685454@[192.168.132.81]> Subject: Exported From Confluence MIME-Version: 1.0 Content-Type: multipart/related; boundary="----=_Part_2953_585456888.1594831685451" ------=_Part_2953_585456888.1594831685451 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Content-Location: file:///C:/exported.html Custom Expression Examples

# Custom Expression Examples

=20 =20

## Apply Fun= ctions to Simple Metrics

#### Example: Total number of teachers and students.

Add the count of teachers to the count of students.

=20
```[Distin=
ct Count: Teacher ID] + [Distinct Count: Student ID]
```
=20

#### Example: Rounding the average test score to two decimal pla= ces.

Take the average test score and apply the `round(x, p)` funct= ion, setting the precision `p` equal to 2.

=20
```round([=
Average: Test Score], 2)
```
=20

#### Example: What is the average of all dimensional scores?

Use variables to hold the total of each dimensional score. Then add all = of those scores to get the overall total. Then divide the overall total by = the number of test sessions in order to calculate the average. Finally, app= ly the `round(x, p)` function to round to two decimal places.

You must use the semi-colon (;) character to separate each line in the e= xpression.

When an expression contains multiple lines, the expression that appears = on the last line (in this example, the line ```round(overallAverage, 2)<= /code>) will be the final result returned.```

``` ```
``` ```
``` =20 dim1Tot= al =3D [Total: Dimension 1 Score]; dim2Total =3D [Total: Dimension 2 Score]; dim3Total =3D [Total: Dimension 3 Score]; dim4Total =3D [Total: Dimension 4 Score]; dim5Total =3D [Total: Dimension 5 Score]; overallTotal =3D sum(dim1Total, dim2Total, dim3Total, dim4Total, dim5Total)= ; overallCount =3D [Count: Session ID]; overallAverage =3D overallTotal / overallCount; round(overallAverage, 2) =20 Compare two or = more Metrics Example: = Student-Teacher ratio. The number of students divided by the number of teachers. Distinct count= s must be used here if the data contains more than one row per student or t= eacher. =20 [Distin= ct Count: Student ID] / [Distinct Count: Teacher ID] =20 Us= e a Comparison to Create a Textual Result Example: Which scoring dimension had the lowest average sco= re? Use variables to hold the average of each of the dimensional scores. Use= min(...) function to find the lowest dimensional score. Use n= ested if (cond, trueValue, falseValue) blocks to return which = dimension matches the lowest score. Note: If more than one dimension shares= the same lowest score, this only returns the first dimension found. =20 dim1Avg= =3D [Average: Dimension 1 Score]; dim2Avg =3D [Average: Dimension 2 Score]; dim3Avg =3D [Average: Dimension 3 Score]; dim4Avg =3D [Average: Dimension 4 Score]; dim5Avg =3D [Average: Dimension 5 Score]; lowestScore =3D min(dim1Avg, dim2Avg, dim3Avg, dim4Avg, dim5Avg); result =3D if (dim5Avg =3D=3D lowestScore, "Dimension 5", "Unknown"); result =3D if (dim4Avg =3D=3D lowestScore, "Dimension 4", result); result =3D if (dim3Avg =3D=3D lowestScore, "Dimension 3", result); result =3D if (dim2Avg =3D=3D lowestScore, "Dimension 2", result); result =3D if (dim1Avg =3D=3D lowestScore, "Dimension 1", result); =20 Example: Indicate "Lo= w", "Medium", and "High" Student-Teacher ratios. Use a variable to record the student-teacher ratio. Then set variables t= o mark the points at which the ratio is considered medium or high. Finally,= use two if (cond, trueValue, falseValue) blocks to return the= "Low", "Medium" or "High" text based on the ratio value. =20 stRatio= =3D [Distinct Count: Student ID] / [Distinct Count: Teacher ID]; mediumThreshold =3D 20; highThreshold =3D 40; if (stRatio < mediumThreshold, "Low", if (stRatio >=3D highThreshold, "High", "Medium" ) ) =20 Ex= ample: Add styling when indicating the "Low", "Medium", and "High" Student-= Teacher ratios. This example is the same as above, but applies green, red, and yellow co= lors to the text. It also makes the word "High" appear in bold formatting.<= /p> =20 stRatio= =3D [Distinct Count: Student ID] / [Distinct Count: Teacher ID]; mediumThreshold =3D 20; highThreshold =3D 40; if (stRatio < mediumThreshold, "<span style=3D\"color:green\">Low</span>", if (stRatio >=3D highThreshold, "<span style=3D\"color:red;font-weight:bold\">High</span&g= t;", "<span style=3D\"color:yellow\">Medium</span>" ) ) =20 Example: Determine the proficiency level of the average sco= re. Compare the average score of the 1st essay submission to what the desire= d proficiency level (e.g. 4). Then use an if (cond, trueValue, falseV= alue) block to return the text "Below" if the average score is less = than 4, or to return "Above" if the score is equal to or greater than 4. =20 profLev= el =3D 4; if ([Average: Essay 1st Submission Score] < 4, "Below", "Above" ); =20 Example: Indicate Benchmark Levels Using Percent Score Use a variable to record the percent score. Then set variables to mark t= he points at which the benchmark level is considered a 1 or higher. Finally= , use an if (cond, trueValue, falseValue) blocks to = return the 1, 2, 3, 4 values text based on the benchmark level value. =20 score= =3D [Average: Objective Percent Score]; b1=3D25; b2=3D50; b3=3D75; b4=3D100; if(score<=3Db1,"1",if(score<=3Db2,"2",if(score<=3Db3,"3",if(score&= lt;=3Db4,"4","N/A")))) ;=20 Example: Indicate w= hether the proficiency level has "Improved", remained "Unchanged", or "Got = Worse" from the first essay submission compared to the most recent. Use a variable to set the desired proficiency level (e.g. 4). Then creat= e two variables, before and after which indicate = the proficiency level of the 1st essay submission and most recent submissio= n, respectively. Finally, use nested conditional blocks to return "Improved= " if the proficiency went from "Below" to "Above", "Unchanged" if it remain= ed the same, or "Got Worse" if it went from "Above" to "Below". =20 profLev= el =3D 4; before =3D if ([Average: Essay 1st Submission Score] < profLevel, "Below", "Above" ); after =3D if ([Average: Essay Most Recent Score] < profLevel, "Below", "Above" ); if (before =3D=3D "Below" && after =3D=3D "Above", "Improved", if (before =3D=3D "Above" && after =3D=3D "Below", "Got Worse", "Unchanged" ) ) =20 Using = Metrics from Different Reports Referring to Other Reports By default, metrics used within a custom expression always use the curre= nt report as a filter of the data. However, by qualifying the metric with t= he name of a report, you can force the filters of that report to be applied= when calculating the metric. For example, this represents the average score within the current report= : =20 [Averag= e: Score] =20 This represents the average score in the state of Utah, regardless of wh= at the current report is: (assuming there is a report named "Utah Students"= ) =20 [Averag= e: Utah Students: Score] =20 Example: Percentage of students who are proficient. This expression requires that the Proficient Students r= eport first be created. This report must contain what you would consider to= be the proficient students, such as those with an "Average Most Recent Sub= mission Score" between 4 and 6. Caution on filtering by aggregates. You can not yet filter by an aggregate that is calculated on the fly. So= if you have a report with a Score field, you can not crea= te a filter that says "students who had an Average Score &= gt;=3D 4". You will need to have the Average Score as a pr= e-computed field in the data set. Be careful of what filters are being compared. In this example, countOfMyStudents refers to all students w= ithin the current report. Keep in mind that the Proficient Students= report may only be applying a filter against the Score field. Therefore, if you apply this expression to a report named Utah Students, but the Proficient Students r= eport does not also filter by Utah, then you will be comparing the number o= f Utah students to all Proficient students. Currently, the solution is to c= reate a Utah Proficient Students report. =20 countOf= ProficientStudents =3D [Distinct Count: Proficient Students: Student ID]; countOfMyStudents =3D [Distinct Count: Student ID]; percentageProficient =3D round(countOfProficientStudents / countOfMyStudent= s * 100, 2) =20 Example: Measuring distance from a desired goal, expr= essed as a ratio. Here we are taking the ratio of the current percentage of proficiency co= mpared to the desired percentage. For example, if currently 20% of students are proficient, and our goal i= s 80%, we could say we are 25% of the way to our goal (that is, 20 / 80). =20 countOf= ProficientStudents =3D [Distinct Count: Proficient Students: Student ID]; countOfMyStudents =3D [Distinct Count: Student ID]; percentageProficient =3D round(countOfProficientStudents / countOfMyStudent= s * 100, 2); percentageGoal =3D 80; round(percentageProficient / percentageGoal * 100, 2) =20 Example: Measuring distance from a desired goal,= expressed as a difference. Here we are taking the difference between our current percentage of prof= iciency and the desired percentage. If currently 20% of students are proficient, and our goal is 80%, we cou= ld also say that we need 60% more students to become proficient to meet our= goal (80 - 20). =20 countOf= ProficientStudents =3D [Distinct Count: Proficient Students: Student ID]; countOfMyStudents =3D [Distinct Count: Student ID]; percentageProficient =3D round(countOfProficientStudents / countOfMyStudent= s * 100, 2); percentageGoal =3D 80; round(percentageGoal - percentageProficient, 2) =20 Using Overall Metrics from Different Reports Referring to Overall Calculations By default, when a custom expression is applied to a pivot table, the ca= lculation will be aggregated against each unique set of values that are bei= ng grouped on. However, in some cases you may want to compare these aggrega= tes with an overall aggregate. You can refer to the overall aggregate of an= y metric by including the word "Overall" at the beginning of the name. In this example, this calculation will be aggregated according to how th= e expression is applied to a pivot table: =20 [Averag= e: Score] =20 Now in this example, we explicitly desire to use the overall average sco= re, regardless of how this expression is being grouped within a pivot table= : =20 [Overal= l Average: Score] =20 The power of using the overall aggregate becomes clear in the example be= low. Example: Benchmarking my = schools' performance against state-wide or application-wide performance. In these examples, All Sessions is a report that applie= s no filters so it includes all scores within the application. Utah= Students is a report that filters by the state of Utah, so only s= tudents from Utah are included. Application-wide comparison: =20 appwide= Average =3D [Overall Average: All Sessions: Essay Score]; reportAverage =3D [Average: Essay Score]; round(reportAverage - appwideAverage, 2) =20 State-wide comparison: =20 stateAv= erage =3D [Overall Average: Utah Students: Essay Score]; reportAverage =3D [Average: Essay Score]; round(reportAverage - appwideAverage, 2) =20 Handling Mis= sing Input Values Some of your data may contain missing values.  If so, there is an i= sNull() function that can be used to change the outcome of the expression b= ased on the presence of a missing value.  The following example genera= tes a text results based on the Age field but does not handle missing value= s: =20 if ( [Age] >=3D 50, "Over the Hill", "Under the Hill" )=20 Bu= t if any records were missing a value for Age, then the expression would th= row an error. The following improvement handles missing values: =20 if ( isNull([Age]), "Don't Know", if ( [Age] >=3D 50, "Over the Hill", "Under the Hill" ) )=20 ```
``` ------=_Part_2953_585456888.1594831685451-- ```