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.
