# New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. 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
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)