Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

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:

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!

1 Solution

Accepted Solutions
TKendrick20
Valued Contributor

Re: Distinctly Counting Students in Calculated Dimension

I think this will do it. The key is the AGGR function.

Dimension:

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


Measure:

Count(Distinct StudentKey)

4 Replies
TKendrick20
Valued Contributor

Re: Distinctly Counting Students in Calculated Dimension

To start, you may be better off with the expression:

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

Not applicable

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?

TKendrick20
Valued Contributor

Re: Distinctly Counting Students in Calculated Dimension

I think this will do it. The key is the AGGR function.

Dimension:

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


Measure:

Count(Distinct StudentKey)

Not applicable

Re: Distinctly Counting Students in Calculated Dimension

It worked, thank you!! The data had multiple records for the same events, so having distinct also made a big difference to make the results more accurate. Thanks for all your help Tim!

In case it helps anyone else, one of my coworkers came up with an alternate solution by using the class function in front of AGGR:

If(Class(Aggr(Count(distinct EventCode),StudentKey),$(vFreqInterval)) > $(vFreqUB), '>'&$(vFreqUB),

    Num(Class(Aggr(Count(distinct EventCode),StudentKey),$(vFreqInterval))))


Using variables vFreqInterval and vFreqUB, it allows the users to choose their upper bound and frequency interval on the chart. Just a way to make it more interactive for the user

Community Browser