Add the count of teachers to the count of students.
Take the average test score and apply the
round(x, p) function, setting the precision
p equal to 2.
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.
You must use the semi-colon (;) character to separate each line in the expression.
When an expression contains multiple lines, the expression that appears on the last line (in this example, the line
round(overallAverage, 2)) will be the final result returned.
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.
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.
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.
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.
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.
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.
Use a variable to set the desired proficiency level (e.g. 4). Then create two variables,
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".
Referring to Other Reports
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")
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.
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 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.
Be careful of what filters are being compared.
In this example,
countOfMyStudents refers to all students within 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 report does not also filter by Utah, then you will be comparing the number of Utah students to all Proficient students. Currently, the solution is to create a Utah Proficient Students report.
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).
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).
Referring to Overall Calculations
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.
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.
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:
But if any records were missing a value for Age, then the expression would throw an error. The following improvement handles missing values: