4 Replies Latest reply: Jul 13, 2017 10:10 AM by Vera Ogdanets RSS

    Distinctly Counting Students in Calculated Dimension

    Vera Ogdanets

      The objective is to create a bar chart showing the amount of students who took 1 class, 2 classes, 3 classes, 4 classes, and 5 or more.

       

      I have two excel sheets: event and participant.

       

      The event sheet is a list of classes offered with EventCode as an identifier. The participant sheet is a list of students registered for classes over time, with EventCode referencing the class they registered for. Each time they register for a new class, a new record (row) is created.

       

      There is no consistent identifier for students, so in the case that a student repeats in the participant sheet, I created a Key by joining the First Name, Last Name, DOB, and City in the load script.

       

      The issue I am coming across is creating a calculated dimension that will display the students that repeat classes split up into the range: 1, 2, 3, 4, and 5 or more classes on the x axis of a bar chart.


      I have an expression which works, but only in a table:

       

      if(count(distinct EventCode)=1,'1',

      if(count(distinct EventCode)=2,'2',

      if(count(distinct EventCode)=3,'3',

      if(count(distinct EventCode)=4,'4',

      if(count(distinct EventCode)>4,'5+')))))

       

      For example, I have a table like this. The range column is using the expression above.

       

      Student Key

      Number of Classes Taken

      Count(distinct EventCode)

      Range

      111
      211
      322
      422
      522
      633
      733
      895+

       

      But if I try to put the Range expression in a dimension to create the bar chart, it gives me the error of an invalid dimension.

       

      I've tried counting the repeating students in the load script using preceding load and resident tables, but neither option worked. The count essentially needs to be done in the dashboard, not the load script, otherwise it counts up the totals instead of each individual student.

       

      My latest attempt is creating an inline table in the load script and attempting to assign values that way.

       

      Load Script:

      [Attendance Count Label]:

      load * inline

      [AttendanceCountLabel,AttendanceCountExp

      1,1

      2,2

      3,3

      4,4

      5+,5];

       

      Then I created a pivot table. Except it will calculate all of the values, and doesn't distribute them accordingly.

       

      AttendanceCountLabelCount({$ <Key = {"=Count(distinct EventCode)>5"}>}Key)
      149057
      249057
      349057
      449057
      5+49057

       

       

      Any help with this is greatly appreciated!! I've been working on this for a while and I feel like the solution is much simpler than I'm making it out to be. If more clarification is needed, let me know in the comments. Many thanks!