topic Re: Fractile / Quartile in pivot table dimension in QlikView

Fractile / Quartile in pivot table dimension

Wed, 09 Oct 2013 05:41:44 GMT

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.

Re: Fractile / Quartile in pivot table dimension

lironbaram — Wed, 09 Oct 2013 06:15:22 GMT

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

Re: Fractile / Quartile in pivot table dimension

Wed, 09 Oct 2013 06:19:20 GMT

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.

Re: Fractile / Quartile in pivot table dimension

saumyashah90 — Wed, 09 Oct 2013 06:31:34 GMT

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)