Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

gameoverzx
New Contributor III

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:

SchoolStudentID
A1
A2
A3
A3
A4
A5
B6
B7
B8
C9
C9
C10
C11
C12
C13
C14
C15
C16
C17
C18

  

ActionStudentID
Sign card1
Water Balloon1
Playground2
Sign card2
Water Balloon2
Playground2
Sign card3
Water Balloon3
Playground1
Sign card6
Water Balloon6
Playground6
Sign card6
Water Balloon9
Playground12
Sign card12
Water Balloon12
Playground12
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
gameoverzx
New Contributor III

Re: FirstSortedValue to determine KPI

Have figured out why.

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

The following is the answer:

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

1 Reply
gameoverzx
New Contributor III

Re: FirstSortedValue to determine KPI

Have figured out why.

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

The following is the answer:

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

Community Browser