# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Announcement: Certain actions are currently causing a Page Not Found error. This is a known issue and we are working with the platform vendor to investigate and resolve it.
Highlighted
Partner

## FirstSortedValue to determine KPI

Hi all,

I want to determine a school that has the highest turn up rate for an event.

However, data is captured for each action done by a student in the event.

Every student is assumed to have done at least one action if they turn up for the event.

I have tables as follows:

 School StudentID A 1 A 2 A 3 A 3 A 4 A 5 B 6 B 7 B 8 C 9 C 9 C 10 C 11 C 12 C 13 C 14 C 15 C 16 C 17 C 18

 Action StudentID Sign card 1 Water Balloon 1 Playground 2 Sign card 2 Water Balloon 2 Playground 2 Sign card 3 Water Balloon 3 Playground 1 Sign card 6 Water Balloon 6 Playground 6 Sign card 6 Water Balloon 9 Playground 12 Sign card 12 Water Balloon 12 Playground 12 Playground 20

In this case, turn up rate for each school is as follows:

School A = 60% (3/5) [StudentID: 1 2 3]

School B = 33% (1/3) [StudentID: 6]

School C = 20% (2/10) [StudentID: 9 12]

I want my KPI to show School A.

I have tried the following:

=FirstSortedValue(School, -Aggr(count(distinct {\$<[Action]-={"NULL"}>} StudentID)/count(distinct {1} StudentID), School))

// Unable to get value when i add the set expression where action is not null

// count(distinct {\$<[Action]-={"NULL"}>} StudentID) ensures that i only count the distinct amount of students who did an action, where action is not null

// count(distinct {1} StudentID) the {1} is needed so that when i do selection, the dominator does not change.

Any help is greatly appreciated.

1 Solution

Accepted Solutions
Partner

## Re: FirstSortedValue to determine KPI

Have figured out why.

Because i did not account for when the studentID does not belong to any school.

=FirstSortedValue(School, -Aggr(count(distinct {\$<[Action]-={"NULL"}, [School] -= {"NULL"}>} StudentID)/count(distinct {1} StudentID), School))