How to use The Aggr function?

[#1]

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

[swuehl]

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.