6 Replies Latest reply: Apr 6, 2018 8:55 AM by Sunny Talwar RSS

    Calculating aggr while ignoring some dimensions in pivot table.

    Steven Taschereau

      Given two tables:

       

      subjectgroup
      Abeapple

      Andy

      apple
      Bettybanana
      Brendabanana
      Charleycherry
      Candicecherry
      Craigcherry

       

      subject

      eventinstance
      Abeartery pop1
      Abeartery pop2
      Abeangioplasty1
      Andyangioplasty1
      Andybiopsy1
      Bettybiopsy1
      Charleyartery pop1
      Charleycranial leak1
      Craighernia1

       

      I want to create a pivot table where the rows are the events, the columns are the groups and under each column are 3 measures

      1) count of instances of the event for that group; count( subject)

      2) count of subjects experiencing that event; count (distinct subject)

      3) count of subjects experiencing that event / total number of subjects in the group (irrespective of events)

       

      Demo4.png

      I've got it all working up  until #3.  I can't get  the expression to count total subjects in the group

       

      Thanks for any help.

      Steve