Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have data something like this.
Person | Sales Amount | Cummulative Sum | ||||
A | 830 | 830 | 13.0% | Top 25% | Top 50% | |
B | 760 | 1590 | 24.9% | |||
C | 750 | 2340 | 36.6% | |||
D | 700 | 3040 | 47.6% | |||
E | 600 | 3640 | 57.0% | Top 75% | ||
F | 500 | 4140 | 64.8% | |||
G | 450 | 4590 | 71.8% | |||
H | 400 | 4990 | 78.1% | |||
I | 350 | 5340 | 83.6% | |||
J | 300 | 5640 | 88.3% | |||
K | 250 | 5890 | 92.2% | |||
L | 200 | 6090 | 95.3% | |||
M | 150 | 6240 | 97.7% | |||
N | 100 | 6340 | 99.2% | |||
O | 50 | 6390 | 100.0% | |||
Total | 6390 | |||||
If you someone select Person B, It should give us, whether that person is on to 25% or 50%, or 75% |
There are multiple level of heirarchy and calendar is associated with Person, so Please help me solving on front end.
Thanks,
Hi smartech,
You need to use the constant 1 in set analysis to force qlikview to retrieve the correct value when you select person B.
See the attached file. Hope this helps you.
Regards
HI Jeff,
Thank you for your response. As I wrote before, thare are many mroe dimension associated with the Person, I can not do a cummulative sum on the back end script.
Could you please think some other way?
Thanks,
Hi smartech,
You can create a cummulative sum in an expression as follow:
sum(aggr(rangesum(above( sum({1}[Sales Amount]),0,RowNo())),Person))
The following post http://community.qlik.com/blogs/qlikviewdesignblog/2012/10/12/accumulative-sums contains more detail about how to create an accumulative sum.
See the file attached file. Hope this helps you.
Regards
Hi Jeff,
The above example works fine for decending order of the sales amount. However, if the order is not defined properly, it is not working as desired.
Could you please advice.
Thanks,