Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
dselgo_eidex
Partner - Creator III
Partner - Creator III

Calculate measure over complement of dimension value

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:

GroupScore
A100
A200
B50
B50
B100
C100

 

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:

GroupAvg ScoreAvg Score'
A150(50 + 50 + 100 + 100) / 4 = 75
B50(100 + 200 + 100) / 3 = 133.3
C100(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?

Labels (3)
1 Solution

Accepted Solutions
Kushal_Chawda

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

View solution in original post

6 Replies
sunny_talwar

Try this expression

=Pick(
	Match([Group], 'A', 'B', 'C'),
    Avg(TOTAL {$<[Group] -= {'A'}>} [Score]),
    Avg(TOTAL {$<[Group] -= {'B'}>} [Score]),
    Avg(TOTAL {$<[Group] -= {'C'}>} [Score])
)

image.png 

dselgo_eidex
Partner - Creator III
Partner - Creator III
Author

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.

sunny_talwar

Would you be able to share a more realistic model of your dashboard where we can see the problem you might be running into?

dselgo_eidex
Partner - Creator III
Partner - Creator III
Author

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.

Kushal_Chawda

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

dselgo_eidex
Partner - Creator III
Partner - Creator III
Author

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.