Qlik Community

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 
Search instead for 
Did you mean: 
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.

CityRN
a6
b15
c4
d5
e8
f7
a6
a5
b2
f5
e3
a9
b12
c4
d8
e15

SUM(RN) = 114

Desired output

QQuartile RangeCityRNCumil.RN
0-25%0-28.5c88
--f1220
25-50%28.5-57d1333
50-75%57-85.5e2659
--a2685
75-100%%85.5-114b2911

Please help me out.

1 Solution

Accepted Solutions
Highlighted
Specialist
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)

View solution in original post

3 Replies
Highlighted
Partner
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
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)

View solution in original post