# New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for
Did you mean:
Highlighted Not applicable

## Fractile / Quartile in pivot table dimension

Hi guys,

I want to create a pivot table with the which has 2 fields. Cities and Room nights.

The table needs to be devided into quartiles. according to room nights. There are multiple entries for each city. The data will look something like this.

 City RN a 6 b 15 c 4 d 5 e 8 f 7 a 6 a 5 b 2 f 5 e 3 a 9 b 12 c 4 d 8 e 15

SUM(RN) = 114

Desired output

 Q Quartile Range City RN Cumil.RN 0-25% 0-28.5 c 8 8 - - f 12 20 25-50% 28.5-57 d 13 33 50-75% 57-85.5 e 26 59 - - a 26 85 75-100%% 85.5-114 b 29 11

1 Solution

Accepted Solutions
Highlighted Specialist

Dimension City

Expressions

Q=class(rangesum(above(cull).sum(RN))/sum(total RN).0.25)

Quartile=num(right(class(rangesum(above(cull).sum(RN))/sum(total RN).0.25).len(class(rangesum(above(cull).sum(RN))/sum(total RN).0.25))-index(class(rangesum(above(cull).sum(RN)/sum(total RN).0.25).'<'.2)-1))*sum(total RN)

RN=sum(RN)

Cull=rangesum(above(cull).sum(RN)

3 Replies
Highlighted Partner

hi attach is an example

this example uses expressions in order to calculate what you need

i might be better for performance , to do the calculation in the script

Highlighted Not applicable

Hi Liron,

I am using a personal edition of qlikview. So cannot open the file. Could you please put it as a text in the post. It would be very helpful.

Highlighted Specialist

Dimension City

Expressions

Q=class(rangesum(above(cull).sum(RN))/sum(total RN).0.25)

Quartile=num(right(class(rangesum(above(cull).sum(RN))/sum(total RN).0.25).len(class(rangesum(above(cull).sum(RN))/sum(total RN).0.25))-index(class(rangesum(above(cull).sum(RN)/sum(total RN).0.25).'<'.2)-1))*sum(total RN)

RN=sum(RN)

Cull=rangesum(above(cull).sum(RN)  