Top percentage on the text box

[#1]

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,

[jeffmartins]

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

[#3]

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,

[jeffmartins]

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

[#5]

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,