Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Many Thanks
Chris
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)
100
vAttendance=100
Then you dimension becomes
if(100<0.90,'90 Below','Above 90'), and it would always show 'Above 90'.
Hi Sunny,
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.
Thanks
Chris
Thanks,
If I wanted to group results from a calculation would it be better to do this in the load script or the chart ?
Thanks
Chris
yes without grouping your variable always aggregates at all level,
you have to use aggr function Aggr() function
Even if you scroll all the way to the bottom it doesn't show Above 90? Can you may be share your qvf for us to take a look at?