Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
dselgo_eidex
Partner - Creator III
Partner - Creator III

Pivot Table TOTAL with Calculated Dimension

Hello, I am currently working on a complicated Pivot Table. Simply stated, I am trying to group students up into different testing percentile groups (1st - 20th percentile, 21st - 30th percentile, etc.). I've done this easily enough by creating a calculated dimension that uses the class() function to calculate the percentiles and then group them up.

The issue is that I have a second dimension (Student) where I am trying to show the Average Score of the percentile group. Normally, this would be easy and I would just use the TOTAL qualifier. But since I am using a calculated dimension, I can't get the expression to calculate over the percentile groups.

Here is what I have with no rows expanded:
image.png

And when I expand a row I expect to see this (I have hard coded the expression to 89.7 as an example):
image.png

Dimensions:

Percentile Group: 

=replace(replace(replace(
    class(aggr({$} 
        (((
            count({$} DISTINCT TOTAL [Student]) - 
            rank(TOTAL avg({$} [Score]), 3)
        ) / 
        count({$} DISTINCT TOTAL [Student])
        ) * 100), [Student]
    ), 20), '0 <= x < ', '1st - '), '0', '0th Percentiles'), '10th Percentiles0th Percentiles', '99th Percentiles'
)

Student: [Student]

 

Measures:

Average Score: =avg({$} [Score])

 

Normally for the measure I would just use avg({$} TOTAL<[dimension 1]> [Score]). But since that dimension is a calculated dimension, I am unable to.

Labels (3)
0 Replies