Set analysis with max() function within pivot table

Frank_the_Qlikker — Mon, 26 Jan 2026 18:19:17 GMT

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

Re: Set analysis with max() function within pivot table

edwin — Tue, 27 Apr 2021 19:38:34 GMT

try this:

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

Re: Set analysis with max() function within pivot table

edwin — Tue, 27 Apr 2021 19:39:22 GMT

Re: Set analysis with max() function within pivot table

Frank_the_Qlikker — Wed, 28 Apr 2021 07:38:55 GMT

Brilliant!!! Thanks a lot, Edwin!👍

topic Re: Set analysis with max() function within pivot table in QlikView

Set analysis with max() function within pivot table

Re: Set analysis with max() function within pivot table

Re: Set analysis with max() function within pivot table

Re: Set analysis with max() function within pivot table