Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
v2 = sum({}[Size])
v3 = sum({
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({
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!
Try rangeavg(sum({}[Size]), sum({}[Size]), sum({
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({
)
Try rangeavg(sum({}[Size]), sum({}[Size]), sum({
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({
)
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.
Thanks for the additional insight. Fortunately in my case the data is of the same size.