How to average the values for alternate states?

[Anonymous]

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!

[Gysbert_Wassenaar · Accepted solution]

     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())

)

[simondachstr]

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.

[Anonymous]

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