Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a dashboard that displays various different pieces of information.
I have some tabs that show various KPIs for departments including one that is a combo chart that displays the median average for compliance within a department.
The expression it uses is
Median(TOTAL AGGR(Count({<Ward={'NSEC03'},[Questions Complete]={'Yes'},[Ward transferred to]-={'NSEC07'}>}Ward)/Count({<Ward={'NSEC03'}, [Ward transferred to]-={'NSEC07'}>}Ward),[PAS Date]))
This works perfectly in the chart and displays a line showing an average of 50%
However i also have a tab that shows a straight table that i wish to show these averages in a league table sort of way.
I am able to get the other different elements to work but i cant work out the syntax for calculating the average.
I have tried:
Median(AGGR( Count({<[Questions Complete]={'Yes'}>}FromCorrectedWard)/Count(FromCorrectedWard),FromCorrectedWard))
Which shows 41%
and
Median(AGGR( Count({<[Questions Complete]={'Yes'}>}FromCorrectedWard)/Count(FromCorrectedWard),[PAS Date]))
Which shows 8.2%
The first expression is closer to what i think the calculation needs to be whilst the second is closer to the original KPI expression.
Does anybody have any ideas?
Thanks.
May bad, I mean this
Median(TOTAL <Ward> AGGR(Count({<[Questions Complete] = {'Yes'}, [Ward transferred to] -= {'NSEC07'}>} Ward)/Count({<[Ward transferred to] -= {'NSEC07'}>} Ward), [PAS Date], [Ward]))
Would you be able to share a sample to take a look at the issue?
So i can post these images if that would help?
Obviously the bottom image is the KPI graph with the correct Median Average showing and the top image is the new Straight Table i'm trying to create.
May be this
Median(TOTAL <Ward> AGGR(Count({<Ward={'NSEC03'},[Questions Complete]={'Yes'},[Ward transferred to]-={'NSEC07'}>}Ward)/Count({<Ward={'NSEC03'}, [Ward transferred to]-={'NSEC07'}>}Ward),[PAS Date], [Ward]))
But i need it to show the median average of each department, using yours it would only show me the median average for the one department.
May bad, I mean this
Median(TOTAL <Ward> AGGR(Count({<[Questions Complete] = {'Yes'}, [Ward transferred to] -= {'NSEC07'}>} Ward)/Count({<[Ward transferred to] -= {'NSEC07'}>} Ward), [PAS Date], [Ward]))
Thank you, with a small alteration I've got it working:
Median(TOTAL <FromCorrectedWard> AGGR( Count({<[Questions Complete]={'Yes'}>}FromCorrectedWard)/Count(FromCorrectedWard),[PAS Date], FromCorrectedWard))