How to use The Aggr function?

Tue, 13 Aug 2013 13:53:06 GMT

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!!!!

Re: How to use The Aggr function?

swuehl — Tue, 13 Aug 2013 15:31:11 GMT

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.

topic How to use The Aggr function? in QlikView

How to use The Aggr function?

Re: How to use The Aggr function?