topic Trouble with getting average over selected months in QlikView

Trouble with getting average over selected months

Wed, 12 May 2010 17:25:41 GMT

I am still fairly new to Qlikview so hopefully I can articulate my question. Currently I am displaying a field that has this

count(distinct Member)

And this will display the unique member count over the selected period of time (the selection is always in whole months). What I would like to display is the average number of members per month or:

(count(distinct (Members for first month selected)) + count(distinct (Members for the second month selected)) +.......+ count( distinct (Members for the Last month selected)))/ count(distinct [Submit MonthStart Dt]))

The denominator I know how to do and that will display the month count for me. The Numerator is the part I am having a problem with. I am trying to make it so no matter how many months are selected the same piece of code will work. Does anyone know a way I can do this?

Thank you for looking at my issue,

Jon

Trouble with getting average over selected months

Wed, 12 May 2010 18:10:18 GMT

hi Jon,

use AGGR function.

Something like this:



=Sum(aggr(count(distinct Member),YourMonths)) / Count(distinct YourMonths)

Alex

Trouble with getting average over selected months

johnw — Thu, 13 May 2010 01:07:12 GMT

Yep. Or even more simply:

avg(aggr(count(distinct Member),YourMonths))

The aggr() function essentially tells it to make a little temporary table with "YourMonths" as the dimension, and count(distinct Member) as the expression. Then you tell it you want the avg() of those, giving you your average members per month. You don't actually need to count distinct YourMonths yourself, because that's exactly how many rows the little temporary table will have, so that's what the avg() will use. (Crossing fingers that I'm right. *chuckle*)