Page History
| Wiki Markup | |
|---|---|
| Panel | |
|
Apply Functions to Simple Metrics
Example: Total number of teachers and students.
Add the count of teachers to the count of students.
| Code Block |
|---|
[Distinct Count: Teacher ID] + [Distinct Count: Student ID]
{code}
h4. |
Example:
...
Rounding
...
the
...
average
...
test
...
score
...
to
...
two
...
decimal
...
places.
...
Take
...
the
...
average
...
test
...
score
...
and
...
apply
...
the
...
round(x,
...
p)
...
function,
...
setting
...
the
...
precision
...
p
...
equal
...
to
...
2.
...
| Code Block |
|---|
round([Average: Test Score], 2)
{code}
h4. |
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,
...
apply
...
the
...
round(x,
...
p)
...
function
...
to
...
round
...
to
...
two
...
decimal
...
places.
...
| Tip |
|---|
You must use the semi-colon ( \;) character to separate each line in the expression. {tip} {tip}When an expression contains multiple lines, the expression that appears on the last line (in this example, the line {{ |
| Tip |
|---|
When an expression contains multiple lines, the expression that appears on the last line (in this example, the line
) will be the final result returned. {tip} {code} |
| Code Block |
|---|
dim1Total = [Total: Dimension 1 Score];
dim2Total = [Total: Dimension 2 Score];
dim3Total = [Total: Dimension 3 Score];
dim4Total = [Total: Dimension 4 Score];
dim5Total = [Total: Dimension 5 Score];
overallTotal = sum(dim1Total, dim2Total, dim3Total, dim4Total, dim5Total);
overallCount = [Count: Session ID];
overallAverage = overallTotal / overallCount;
round(overallAverage, 2)
{code}
h2. Compare two or more Metrics
h4. Example: |
Compare two or more Metrics
Example: Student-Teacher
...
ratio.
...
The
...
number
...
of
...
students
...
divided
...
by
...
the
...
number
...
of
...
teachers.
...
Distinct
...
counts
...
must
...
be
...
used
...
here
...
if
...
the
...
data
...
contains
...
more
...
than
...
one
...
row
...
per
...
student
...
or
...
teacher.
...
| Code Block |
|---|
[Distinct Count: Student ID] / [Distinct Count: Teacher ID]
{code}
h2. Use a Comparison to Create a Textual Result
h4. Example: Which scoring dimension had the lowest average score?
Use variables to hold the average of each of the dimensional scores. Use {{ |
Use a Comparison to Create a Textual Result
Example: Which scoring dimension had the lowest average score?
Use variables to hold the average of each of the dimensional scores. Use min(...)
...
function
...
to
...
find
...
the
...
lowest
...
dimensional
...
score.
...
Use
...
nested
...
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.
...
| Code Block |
|---|
dim1Avg = [Average: Dimension 1 Score]; dim2Avg = [Average: Dimension 2 Score]; dim3Avg = [Average: Dimension 3 Score]; dim4Avg = [Average: Dimension 4 Score]; dim5Avg = [Average: Dimension 5 Score]; lowestScore = min(dim1Avg, dim2Avg, dim3Avg, dim4Avg, dim5Avg); result = if (dim1Avgdim5Avg == lowestScore, "Dimension 15", "Unknown"); result = if (dim2Avgdim4Avg == lowestScore, "Dimension 24", result); result = if (dim3Avg == lowestScore, "Dimension 3", result); result = if (dim4Avgdim2Avg == lowestScore, "Dimension 42", result); result = if (dim5Avgdim1Avg == lowestScore, "Dimension 51", "Unknown" ) ) ) ) ) {code} h4. Example: Indicate result); |
Example: Indicate "Low",
...
"Medium",
...
and
...
"High"
...
Student-Teacher
...
ratios.
...
Use
...
a
...
variable
...
to
...
record
...
the
...
student-teacher
...
ratio.
...
Then
...
set
...
variables
...
to
...
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.
...
| Code Block |
|---|
stRatio = [Distinct Count: Student ID] / [Distinct Count: Teacher ID];
mediumThreshold = 20;
highThreshold = 40;
if (stRatio < mediumThreshold,
"Low",
if (stRatio >= highThreshold,
"High",
"Medium"
)
)
{code}
h4. |
Example:
...
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
...
colors
...
to
...
the
...
text.
...
It
...
also
...
makes
...
the
...
word
...
"High"
...
appear
...
in
...
bold
...
formatting.
...
| Code Block |
|---|
stRatio = [Distinct Count: Student ID] / [Distinct Count: Teacher ID];
mediumThreshold = 20;
highThreshold = 40;
if (stRatio < mediumThreshold,
"<span style=\"color:green\">Low</span>",
if (stRatio >= highThreshold,
"<span style=\"color:red;font-weight:bold\">High</span>",
"<span style=\"color:yellow\">Medium</span>"
)
)
{code}
h4. |
Example:
...
Determine
...
the
...
proficiency
...
level
...
of
...
the
...
average
...
score.
...
Compare
...
the
...
average
...
score
...
of
...
the
...
1st
...
essay
...
submission
...
to
...
what
...
the
...
desired
...
proficiency
...
level
...
(e.g.
...
4).
...
Then
...
use
...
an
...
if
...
(cond,
...
trueValue,
...
falseValue)
...
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.
...
| Code Block |
|---|
profLevel = 4;
if ([Average: Essay 1st Submission Score] < 4,
"Below",
"Above"
);
{code}
h4. Example: Indicate whether 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 create two variables, {{before}} and {{after}} which indicate the proficiency level of the 1st essay submission and most recent submission, respectively. Finally, use nested conditional blocks to return "Improved" if the proficiency went from "Below" to "Above", "Unchanged" if it remained the same, or "Got Worse" if it went from "Above" to "Below".
{code}
|
Example: Indicate Benchmark Levels Using Percent Score
Use a variable to record the percent score. Then set variables to mark the 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.
| Code Block |
|---|
score= [Average: Objective Percent Score];
b1=25;
b2=50;
b3=75;
b4=100;
if(score<=b1,"1",if(score<=b2,"2",if(score<=b3,"3",if(score<=b4,"4","N/A"))))
; |
Example: Indicate whether 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 create two variables, before and after which indicate the proficiency level of the 1st essay submission and most recent submission, respectively. Finally, use nested conditional blocks to return "Improved" if the proficiency went from "Below" to "Above", "Unchanged" if it remained the same, or "Got Worse" if it went from "Above" to "Below".
| Code Block |
|---|
profLevel = 4;
before = if ([Average: Essay 1st Submission Score] < profLevel,
"Below",
"Above"
);
after = if ([Average: Essay Most Recent Score] < profLevel,
"Below",
"Above"
);
if (before == "Below" && after == "Above",
"Improved",
if (before == "Above" && after == "Below",
"Got Worse",
"Unchanged"
)
)
{code}
h2. Using Metrics from Different Reports
h4. Example: Percentage of Students who are Proficient
This expression requires that the *Proficient Students* report first be created. This report must contain what you would consider to be the proficient students, such as those with an "Average Most Recent Submission Score" between 4 and 6.
{warn}Note: 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 create a filter that says "students who had an *Average Score* >= 4". You will need to have the *Average Score* as a pre-computed field in the data set.{warn}
{code}
|
Using Metrics from Different Reports
| Tip | ||||
|---|---|---|---|---|
| ||||
By default, metrics used within a custom expression always use the current report as a filter of the data. However, by qualifying the metric with the 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:
This represents the average score in the state of Utah, regardless of what the current report is: (assuming there is a report named "Utah Students")
|
Example: Percentage of students who are proficient.
This expression requires that the Proficient Students report first be created. This report must contain what you would consider to be the proficient students, such as those with an "Average Most Recent Submission Score" between 4 and 6.
| Note | ||
|---|---|---|
| ||
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 create a filter that says "students who had an Average Score >= 4". You will need to have the Average Score as a pre-computed field in the data set. |
| Note | ||
|---|---|---|
| ||
In this example, |
| Code Block |
|---|
countOfProficientStudents = [Distinct Count: Proficient Students: Student ID];
countOfMyStudents = [Distinct Count: Student ID];
percentageProficient = round(countOfProficientStudents / countOfMyStudents * 100, 2)
{code}
h4. |
Example:
...
Measuring
...
distance
...
from
...
a
...
desired
...
goal,
...
expressed
...
as
...
a
...
ratio.
...
Here
...
we
...
are
...
taking
...
the
...
ratio
...
of
...
the
...
current
...
percentage
...
of
...
proficiency
...
compared
...
to
...
the
...
desired
...
percentage.
...
For
...
example,
...
if
...
currently
...
20%
...
of
...
students
...
are
...
proficient,
...
and
...
our
...
goal
...
is
...
80%,
...
we
...
could
...
say
...
we
...
are
...
25%
...
of
...
the
...
way
...
to
...
our
...
goal
...
(that
...
is, 20 / 80).
| Code Block |
|---|
countOfProficientStudents = [Distinct Count: Proficient Students: Student ID];
countOfMyStudents = [Distinct Count: Student ID];
percentageProficient = round(countOfProficientStudents / countOfMyStudents * 100, 2);
percentageGoal = 80;
round(percentageProficient / percentageGoal * 100, 2)
|
Example: Measuring distance from a desired goal, expressed as a difference.
Here we are taking the difference between our current percentage of proficiency and the desired percentage.
If currently 20% of students are proficient, and our goal is 80%, we could also say that we need 60% more students to become proficient to meet our goal (80 - 20).
| Code Block |
|---|
countOfProficientStudents = [Distinct Count: Proficient Students: Student ID];
countOfMyStudents = [Distinct Count: Student ID];
percentageProficient = round(countOfProficientStudents / countOfMyStudents * 100, 2);
percentageGoal = 80;
round(percentageGoal - percentageProficient, 2)
|
Using Overall Metrics from Different Reports
| Tip | ||||
|---|---|---|---|---|
| ||||
By default, when a custom expression is applied to a pivot table, the calculation will be aggregated against each unique set of values that are being grouped on. However, in some cases you may want to compare these aggregates with an overall aggregate. You can refer to the overall aggregate of any metric by including the word "Overall" at the beginning of the name. In this example, this calculation will be aggregated according to how the expression is applied to a pivot table:
Now in this example, we explicitly desire to use the overall average score, regardless of how this expression is being grouped within a pivot table:
The power of using the overall aggregate becomes clear in the example below. |
Example: Benchmarking my schools' performance against state-wide or application-wide performance.
In these examples, All Sessions is a report that applies no filters so it includes all scores within the application. Utah Students is a report that filters by the state of Utah, so only students from Utah are included.
Application-wide comparison:
| Code Block |
|---|
appwideAverage = [Overall Average: All Sessions: Essay Score];
reportAverage = [Average: Essay Score];
round(reportAverage - appwideAverage, 2)
|
State-wide comparison:
| Code Block |
|---|
stateAverage = [Overall Average: Utah Students: Essay Score];
reportAverage = [Average: Essay Score];
round(reportAverage - appwideAverage, 2)
|
Handling Missing Input Values
Some of your data may contain missing values. If so, there is an isNull() function that can be used to change the outcome of the expression based on the presence of a missing value. The following example generates a text results based on the Age field but does not handle missing values:
| Code Block |
|---|
if (
[Age] >= 50,
"Over the Hill",
"Under the Hill"
) |
But if any records were missing a value for Age, then the expression would throw an error. The following improvement handles missing values:
| Code Block |
|---|
if (
isNull([Age]),
"Don't Know",
if (
[Age] >= 50,
"Over the Hill",
"Under the Hill"
)
) |