Skip to main content
Announcements
July 15, NEW Customer Portal: Initial launch will improve how you submit Support Cases. IMPORTANT DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Frank_the_Qlikker
Contributor
Contributor

Set analysis with max() function within pivot table

Hi everyone,

I've been struggling with a set analysis expression witin a pivot table.

I have studentnames over 3 schoolyears with the courses they took. Every course-subsciption has its own Status (100, 200 or 300).

I want to know the number of students with their maximum status of each Schoolyear.

LOAD * inline [
Name, Schoolyear, Status, Course, School
Mark, 2019, 200, Log-1, Logistics
John, 2019, 100, Hc-1, Healthcare
John, 2020, 300, Hc-1, Healthcare
Ivy, 2020, 100, Log-1, Logistics
Ivy, 2020, 300, Log-2, Logistics
Mark, 2021, 100, Hc-1, Healthcare
Mark, 2021, 200, Log-1, Logistics
Mark, 2021, 300, Log-2, Logistics
Mark, 2021, 300, Log-3, Logistics]
;

As a table it looks like this:

Frank_the_Qlikker_4-1619350695116.png

When I create a pivot-table with the Name as a dimension, it works allright:  

Frank_the_Qlikker_0-1619349602293.png

ex. Mark has 4 subscriptions in 2021, and the maximum Status is 300. 

However, when I remove the Name as a dimension, the maximum Status is calculated over ALL students. Thats not what I want.

So I tried this expression:  =count({<Name={"=max(Status)"}>} distinct Name)

Frank_the_Qlikker_3-1619350489455.png

But that's not correct either, because in 2021 there is no Name with the max(Status) =200.  The max(status) of  'Mark' = 300.

What I expected was:

Frank_the_Qlikker_0-1619351966427.png

 

it seems that the max(Status) is calculated beyond the boundaries of the Schoolyear in the pivottable.

Can anyone please help me with this?

Kind regards,

Frank

 

1 Solution

Accepted Solutions
edwin
Master II
Master II

try this:

edwin_0-1619552255328.pngedwin_1-1619552282285.png

=count(DISTINCT if(Status=aggr(NODISTINCT max(Status), Name, Schoolyear),Name))

View solution in original post

3 Replies
edwin
Master II
Master II

try this:

edwin_0-1619552255328.pngedwin_1-1619552282285.png

=count(DISTINCT if(Status=aggr(NODISTINCT max(Status), Name, Schoolyear),Name))

edwin
Master II
Master II

edwin_2-1619552356546.png

 

Frank_the_Qlikker
Contributor
Contributor
Author

Brilliant!!! Thanks a lot, Edwin!👍