Set analysis with max() function within pivot table

[Frank_the_Qlikker]

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:

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

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)

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:

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

[edwin · Accepted solution]

try this:

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

[edwin]

[Frank_the_Qlikker]

Brilliant!!! Thanks a lot, Edwin!👍