Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello, I am trying to look for a way to calculate a measure based on the complement set of values to the dimension value. Say for example, I have the following data table representing a score being assigned to students belonging to different groups:
Group | Score |
A | 100 |
A | 200 |
B | 50 |
B | 50 |
B | 100 |
C | 100 |
What I would like to do is compare each group's average score against the average of all of the scores of students NOT in that group. Essentially, I would like to end up with the following table:
Group | Avg Score | Avg Score' |
A | 150 | (50 + 50 + 100 + 100) / 4 = 75 |
B | 50 | (100 + 200 + 100) / 3 = 133.3 |
C | 100 | (50 + 50 + 100 + 100 + 200) / 5 = 100 |
I know that you should be able to do this using if statements, but it only works if the dimension values are known ahead of time (which for me will not always be the case):
=if([Group] = 'A', avg({$<[Group] -= {'A'}>} [Score]),
if([Group] = 'B', avg({$<[Group] -= {'B'}>} [Score]),
if([Group] = 'C', avg({$<[Group] -= {'C'}>} [Score])
)))
Is this behavior possible in Qlik Sense?
@sunny_talwar would have answered it definitely but I am replying just to get quicker answer to solve the issue. May be sunny can provide better solution as well.
you can try below
=pick(Match(Group,$(=Concat(distinct Group,',',Group))),
$(=Concat(distinct 'avg({$<[Group] -= {'&Group&'}>} TOTAL [Score])',',',Group)))
Try this expression
=Pick(
Match([Group], 'A', 'B', 'C'),
Avg(TOTAL {$<[Group] -= {'A'}>} [Score]),
Avg(TOTAL {$<[Group] -= {'B'}>} [Score]),
Avg(TOTAL {$<[Group] -= {'C'}>} [Score])
)
Ahhh sorry, I forgot to add the TOTAL qualifiers, and I agree that using Pick() would be better than a chain of if statements. However, that solution only works when the dimension values are known ahead of time, and in the actual use case there will be many more values.
Would you be able to share a more realistic model of your dashboard where we can see the problem you might be running into?
Sure thing Sunny. I've attached an example with a table of 100 students randomly belonging to one of 20 different groups.
I want to clarify, the method posted above using pick() is giving me the values that I am expecting, but the problem is that it only works when I know the group values ahead of time. For instance, I might reload the data and a new record gets added where the group is 21. The table would get no value for Group 21 because there is no case for it in the expression. In a more realistic example, my dimension might be a date field that gets updated daily.
What I am trying to find out is if there is a way that I can tell my table to calculate the expression over the complement of the dimension values.
@sunny_talwar would have answered it definitely but I am replying just to get quicker answer to solve the issue. May be sunny can provide better solution as well.
you can try below
=pick(Match(Group,$(=Concat(distinct Group,',',Group))),
$(=Concat(distinct 'avg({$<[Group] -= {'&Group&'}>} TOTAL [Score])',',',Group)))
I forgot you could use dollar-sign expansion like that! I mostly use it inside set analysis and I guess I convinced my brain that was all it could be used for 😋.
I think that this is a very good solution and should work just fine for my purposes. Thanks!
It would be nice to see something similar to the TOTAL qualifier get added in the future to have this effect. I understand it is a pretty niche case, but it would be pretty cool.