1 Reply Latest reply: Jul 19, 2018 11:51 PM by Gaston Tan RSS

    FirstSortedValue to determine KPI

    Gaston Tan

      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.