Distinctly Counting Students in Calculated Dimension
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:
For example, I have a table like this. The range column is using the expression above.
Number of Classes Taken
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.
[Attendance Count Label]:
load * inline
Then I created a pivot table. Except it will calculate all of the values, and doesn't distribute them accordingly.
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!
Re: Distinctly Counting Students in Calculated Dimension
So when I put that in, it's still calculating them for all of the rows, i.e. it shows up as '5+' for all of the rows. I know why it's doing that due to how the expression is set up, but is there a way to have it distribute 1-5+ accordingly?