topic Re: How to average the values for alternate states? in QlikView

How to average the values for alternate states?

Anonymous — Mon, 07 Sep 2015 11:32:16 GMT

Hi Qlikview Experts,

Browsed through discussions here regarding alternate states but can't find anything on averages. I have three alternate states - A, B, C. Not all three states will always have selections.

I have the following variables:

v1 = sum({}[Size])

v2 = sum({}[Size])

v3 = sum({}[Size])

I then want to get the average value for [Size] of those three (keeping in mind also that not all three alternate states are always at use). How would that expression look like?

So far this works:

=(sum({}[Size]) + sum({}[Size]) + sum({}[Size]) )/3

However the hardcoded denominator will only make sense when there are always three alternate states at use, which is not the case.

Hope someone can help! Thanks!

Re: How to average the values for alternate states?

Gysbert_Wassenaar — Mon, 07 Sep 2015 15:19:03 GMT

Try rangeavg(sum({}[Size]), sum({}[Size]), sum({}[Size]) )

Or if you want to check for selections in each state:

rangeavg(

if(len(getcurrentselections ('|', ';', ',', 9, 'A')), sum({}[Size]), Null()),

if(len(getcurrentselections ('|', ';', ',', 9, 'B')), sum({}[Size]), Null()),

if(len(getcurrentselections ('|', ';', ',', 9, 'C')), sum({}[Size]), Null())

)

Re: How to average the values for alternate states?

simondachstr — Mon, 07 Sep 2015 15:23:36 GMT

Also, you will only have a ideal representation of an average if the record count of Size is the same for all alternate states. If not you will get a weighted average as a result.

Re: How to average the values for alternate states?

Anonymous — Tue, 08 Sep 2015 13:38:42 GMT

Thanks for the additional insight. Fortunately in my case the data is of the same size.