Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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)
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
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.
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)