Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi there,
I need to display a count/number of consultants with their avarege scores falling into different ranges with range as a calculated dimension. Problem is that the caclculated range dimension that I am using is looking at the single scores obtained by each consultants and then putting them in those ranges and hence I end up with an inacurate number of consultants in each range.
Calculated Dimention Looks something like this
if(SCORES >= 0 and SCORES <= 9,'0-9 pen pts',
if(SCORES >= 10 and SCORES <= 19,'10-19 pen pts',
if(SCORES >= 20 and SCORES <= 25,'20-25 pen pts',
if(SCORES >= 26 and SCORES <= 30,'26-30 pen pts',
if(SCORES >= 31 and SCORES <= 35,'31-35 pen pts',
if(SCORES >= 36 and SCORES <= 40,'36-40 pen pts','41 upwards pen pts'
)
)
)
)
)
)
and this is obviously going to look at each score per evaluation per consultant(Which is not what I want).
Then the expression looks something like this
Count(if(Date($(vStartDate),'YYYY-MM-DD') <= Date([COMPLETED DATE],'YYYY-MM-DD') and Date([COMPLETED DATE],'YYYY-MM-DD') <= Date($(vEndDate),'YYYY-MM-DD'), "CONSULTANT"))
I understant that if maybe I use the AGGR function for the Expression and the CLASS function for the Dimension I may be helped but all is not giving me the right answers. Please help!!!!
Maybe something like this as calculated dimension:
=class(
aggr(
avg( SCORES),
CONSULTANT
),
5, 'pen pts'
)
and
=count(distinct CONSULTANT)
as expression.
To add the date selection, try adding a set expression like
{<[COMPLETED DATE] ={">=$(vStartDate)<=$(vEndDate)"} > }
to your count (and maybe also to your avg() expression). You might need to add a date() formatting to your date variables to match your field format.