topic Re: decile creation in Water Cooler

decile creation

Anonymous — Wed, 14 Nov 2018 14:31:06 GMT

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

Re: decile creation

sunny_talwar — Wed, 14 Nov 2018 15:09:51 GMT

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

Re: decile creation

Anonymous — Wed, 14 Nov 2018 15:26:01 GMT

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

Re: decile creation

sunny_talwar — Wed, 14 Nov 2018 15:37:32 GMT

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?

Re: decile creation

Anonymous — Wed, 14 Nov 2018 16:01:59 GMT

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

i hope that you can understand.

Re: decile creation

sunny_talwar — Wed, 14 Nov 2018 16:16:10 GMT

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)])

Re: decile creation

Anonymous — Thu, 15 Nov 2018 08:03:33 GMT

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

Re: decile creation

Anonymous — Thu, 15 Nov 2018 09:53:50 GMT

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

thannks