Announcements
July 15, NEW Customer Portal: Initial launch will improve how you submit Support Cases. IMPORTANT DETAILS
cancel
Showing results for
Did you mean:
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.

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.

Kind regards,

Frank

1 Solution

Accepted Solutions
Master II

try this:

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

3 Replies
Master II

try this:

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

Master II

Contributor
Author

Brilliant!!! Thanks a lot, Edwin!👍

Community Browser