Fractile / Quartile in pivot table dimension

[#1]

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

Please help me out.

[lironbaram]

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

[#3]

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.

[saumyashah90 · Accepted solution]

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)