Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
try this:
=count(DISTINCT if(Status=aggr(NODISTINCT max(Status), Name, Schoolyear),Name))
try this:
=count(DISTINCT if(Status=aggr(NODISTINCT max(Status), Name, Schoolyear),Name))
Brilliant!!! Thanks a lot, Edwin!👍