Date: Fri, 29 Mar 2024 14:35:01 -0400 (EDT) Message-ID: <1060002232.2086.1711737301435@[192.168.132.81]> Subject: Exported From Confluence MIME-Version: 1.0 Content-Type: multipart/related; boundary="----=_Part_2085_415134670.1711737301427" ------=_Part_2085_415134670.1711737301427 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Content-Location: file:///C:/exported.html
Add the count of teachers to the count of students.
[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.
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.
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
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.
[Distin= ct Count: Student ID] / [Distinct Count: Teacher ID]=20
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.
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
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.
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
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>
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
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.
profLev= el =3D 4; if ([Average: Essay 1st Submission Score] < 4, "Below", "Above" );=20
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.
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
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".
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
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= :
[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"= )
[Averag= e: Utah Students: Score]=20
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
countOf= ProficientStudents =3D [Distinct Count: Proficient Students: Student ID]; countOfMyStudents =3D [Distinct Count: Student ID]; percentageProficient =3D round(countOfProficientStudents / countOfMyStudent= s * 100, 2)=20
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).= p>
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
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).
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
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:
[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= :
[Overal= l Average: Score]=20
The power of using the overall aggregate becomes clear in the example be= low.
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:
appwide= Average =3D [Overall Average: All Sessions: Essay Score]; reportAverage =3D [Average: Essay Score]; round(reportAverage - appwideAverage, 2)=20
State-wide comparison:
stateAv= erage =3D [Overall Average: Utah Students: Essay Score]; reportAverage =3D [Average: Essay Score]; round(reportAverage - appwideAverage, 2)=20
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:
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:
if ( isNull([Age]), "Don't Know", if ( [Age] >=3D 50, "Over the Hill", "Under the Hill" ) )=20