Could someone explain in a chart how to use a calculated variable in a dimension to produce a column in as table.
in the load script the variable vAttendance is defined and works correctly;
Let vAttendance = '((Sum([Present])+Sum([Activity])+Sum([Study Leave]))/(Sum([Present])+Sum([Authorised])+Sum([UnAuthorised])+Sum([Activity])))';
The above produces a column that contains values in the format of 0.00'
If I then create a table add the vAttendance into the table as a measure then try to reference it in the dimension to create another column using
if((vAttendance)<0.90,'90 Below','Above 90')
This only returns one value for all figures even though some are below 0.90.
I think the variable is not executing in the dimension.
You might need to use Aggr() function on which you are summing your vAttendance
Aggr(If(vAttendance < 0.90, '90 Below', 'Above 90'), DimensionName/s)
This equation won't work :if((vAttendance)<0.90,'90 Below','Above 90')
As you should understand, after you have defined it, vAttendance will have value now which is (for example)
Then you dimension becomes
if(100<0.90,'90 Below','Above 90'), and it would always show 'Above 90'.
Tried it like this and accepts the syntax but still only show the 90 Below on all.
Aggr(If((vAttendance) < 0.90, '90 Below', 'Above 90'), Pupil)
Below is a screen shot of what is happening.
If I wanted to group results from a calculation would it be better to do this in the load script or the chart ?