=20

=20

=20
- =20
- App= ly Functions to Simple Metrics=20 =20
- Compare t= wo or more Metrics=20 =20
- Use a Comparison to Create a Textual Result=20
- =20
- Example: Which scoring dimension had the lowest avera= ge score? =20
- Example: Indica= te "Low", "Medium", and "High" Student-Teacher ratios. =20
- Example: Add styling when indicating the "Low", "Medium", and "High" St= udent-Teacher ratios. =20
- Example: Determine the proficiency level of the avera= ge score. =20
- Example: Indicate Benchmark Levels Using Percent Score<= /li>=20
- Example: Indi= cate whether the proficiency level has "Improved", remained "Unchanged", or= "Got Worse" from the first essay submission compared to the most recent. =20

=20
- = Using Metrics from Different Reports=20 =20
- Using Overall Metrics from Different Reports=20 =20
- Handli= ng Missing Input Values =20

Add the count of teachers to the count of students.

=20

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

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

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--
```