decile creation

[Anonymous]

hi everyone,

we have a DB of fidelity card. Each card has his sales value and we would like to create sales decile. For example in excel:   we order fidelity card for decreasing sales value, then we calculate percentage share, then we calculate cumulate share so the first decile is composed by the ffidelity card those make the first 10%  of cumulate share.

this for each pharmacy.

we try with this 

=if(aggr(Sum(VALORE),TESSERA) <= fractile(TOTAL aggr(Sum(VALORE),TESSERA), 0.10), 1,
if(aggr(Sum(VALORE),TESSERA) <= fractile(TOTAL aggr(Sum(VALORE),TESSERA), 0.20), 2,
if(aggr(Sum(VALORE),TESSERA) <= fractile(TOTAL aggr(Sum(VALORE),TESSERA), 0.30), 3,
if(aggr(Sum(VALORE),TESSERA) <= fractile(TOTAL aggr(Sum(VALORE),TESSERA), 0.40), 4,
if(aggr(Sum(VALORE),TESSERA) <= fractile(TOTAL aggr(Sum(VALORE),TESSERA), 0.50), 5,
if(aggr(Sum(VALORE),TESSERA) <= fractile(TOTAL aggr(Sum(VALORE),TESSERA), 0.60), 6,
if(aggr(Sum(VALORE),TESSERA) <= fractile(TOTAL aggr(Sum(VALORE),TESSERA), 0.70), 7,
if(aggr(Sum(VALORE),TESSERA) <= fractile(TOTAL aggr(Sum(VALORE),TESSERA), 0.80), 8,
if(aggr(Sum(VALORE),TESSERA) <= fractile(TOTAL aggr(Sum(VALORE),TESSERA), 0.90), 9,10)))))))))

but doesn't work.

there is someone that can help me?

thanks

[sunny_talwar]

What issues or errors are you running into when you use this?

[Anonymous]

with istruction writed before we have

Decili	number card
1	891
2	890
3	890
4	891
5	890
6	888
7	892
8	881
9	884
10	908

but what we expect is

Decili	Number card
1	201
2	259
3	400
4	500
5	700
6	985
7	1158
8	2500
9	3890
10	5400

where in the first decile there are less number card then the 10th decile because in the firt decile there are most valueble cards instead in the 10th decile there are low budget cards.

thanks

[sunny_talwar]

Decile function divides your data into 10 equal parts... which is why you are seeing almost the same number of observations within each bucket. I am not sure I understand the logic behind your expected output. Would you be able to share a sample to look at this?

[Anonymous]

In attached you can find a example where i select one pharmacy and the period giuly - october.

i hope that you can understand.

[sunny_talwar · Accepted solution]

Are you using QlikView 12.1 or above or Qlik Sense... you can try this

Dimension

=Aggr(If(RangeSum(Above(Sum([SUM(VALORE) - SALES]), 0, RowNo()))/Sum(TOTAL [SUM(VALORE) - SALES]) < 0.1, 1,
	 If(RangeSum(Above(Sum([SUM(VALORE) - SALES]), 0, RowNo()))/Sum(TOTAL [SUM(VALORE) - SALES]) < 0.2, 2,
	 If(RangeSum(Above(Sum([SUM(VALORE) - SALES]), 0, RowNo()))/Sum(TOTAL [SUM(VALORE) - SALES]) < 0.3, 3,
	 If(RangeSum(Above(Sum([SUM(VALORE) - SALES]), 0, RowNo()))/Sum(TOTAL [SUM(VALORE) - SALES]) < 0.4, 4,
	 If(RangeSum(Above(Sum([SUM(VALORE) - SALES]), 0, RowNo()))/Sum(TOTAL [SUM(VALORE) - SALES]) < 0.5, 5,
	 If(RangeSum(Above(Sum([SUM(VALORE) - SALES]), 0, RowNo()))/Sum(TOTAL [SUM(VALORE) - SALES]) < 0.6, 6,
	 If(RangeSum(Above(Sum([SUM(VALORE) - SALES]), 0, RowNo()))/Sum(TOTAL [SUM(VALORE) - SALES]) < 0.7, 7,
	 If(RangeSum(Above(Sum([SUM(VALORE) - SALES]), 0, RowNo()))/Sum(TOTAL [SUM(VALORE) - SALES]) < 0.8, 8,
	 If(RangeSum(Above(Sum([SUM(VALORE) - SALES]), 0, RowNo()))/Sum(TOTAL [SUM(VALORE) - SALES]) < 0.9, 9, 10))))))))), ([TESSERA (FIDELITY CARD)], (=Sum([SUM(VALORE) - SALES]), DESC)))

Expression

=Count( [TESSERA (FIDELITY CARD)])

[Anonymous]

hello i use Qlik sense and using your formula in dimention, i get only 1 decile (you can see it in attached)

[Anonymous]

Hello thanks very much. i change the field [Sum (value)] with value and now it works.

thannks