Skip to main content
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. 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 creating a pivot table that uses a calculated dimension to form test percentile groups, and another dimension to show the students within those groups. The problem is that I want one of my columns on the pivot table to be the average score for the entire percentile group.

So basically, I want the table to show this when not expanded:
image.png

And then show this when I expand the percentile group (note: I hard-coded the expression to 89.7 to show 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, I would achieve the result I want by making the measure =avg({$} TOTAL<[Dimension]> [Score]), but since I have a calculated dimension, I am unable to do this.

Labels (3)
1 Solution

Accepted Solutions
dselgo_eidex
Partner - Creator III
Partner - Creator III
Author

Got it working with the following expression:

=Top(RangeAvg(Below(avg({$} [Score]), 0, NoOfRows())))

I had been trying expressions similar to the one above for about 6 hours before I posted this question, and then I figured it out 15 minutes later Smiley Very Happy


View solution in original post

1 Reply
dselgo_eidex
Partner - Creator III
Partner - Creator III
Author

Got it working with the following expression:

=Top(RangeAvg(Below(avg({$} [Score]), 0, NoOfRows())))

I had been trying expressions similar to the one above for about 6 hours before I posted this question, and then I figured it out 15 minutes later Smiley Very Happy