Decile analysis combined with another dimension

[#1]

Hi,

I have a table of purchases by week and customer, similar to this one (wiht many more rows [:)]) :

WeekNo	Cust_id	Amount
1	4514	101,2
1	654	25,63
1	4514	45,7,
2	4514	50
2	654	105
2	4514	123

I would like to perform a decile analysis per week. In this analysis, deciles should be calculated independently for each week. For each week I want to find out how many customers, ranked the week purchase amount, are in the 80% first buyers, 16% next or 4% left.

Here is what I've done so far :

- First dimension : "WeekNo"

- Second dimension :

= if(AGGR(SUM(amount),cust_id, WeekNo)

<= Fractile(TOTAL AGGR(SUM(amount), cust_id, WeekNo), 0.8), '80%',

if(AGGR(SUM(amount), cust_id, WeekNo)

<= Fractile(TOTAL AGGR(SUM(amount), cust_id, WeekNo), 0.96), '16%',

'4%'))

The second dimension does not seem to be correct. If I remove the "TOTAL" keyword I get an error, but with the "TOTAL" key word it looks like the decile are calculated over the complete table and per week.

Any idea?

Thanks

Lionel

[gchampion]

I had the same problem and I would love read an answer on this question.

[johnw]

I'm kind of stabbing in the dark without an example to play with, but perhaps where you currently have TOTAL, instead use TOTAL <WeekNo>? That tells the total to respect the difference in week numbers, which sounds on the surface like what you want.

[gchampion]

Here is the example I used to verify the formula.

On the left simulation of the fractile in the LOAD (result expected) and on the right simulation of the fractile in formula.

Your tips works in the measure (top table) but not in the calculate dimension (bottom table) (//Error in calculation...).

Do you have an idea ?

Another question could you please tell me which solution seems to be the best one between LOAD and Formula (if it is possible;-)) ?

Thanx a lot for your answers

[gchampion · Accepted solution]

Ok I found the solution for the formula:

=if(AGGR(sum(montant),compte_id, week)<=aggr(NODISTINCT fractile(TOTAL <week> aggr(sum(montant), compte_id, week),0.80),week),'P-80%',

if(aggr(sum(montant), compte_id, week)<=aggr(NODISTINCT fractile(TOTAL <week> aggr(sum(montant), compte_id, week),0.96),week),'M-16%','G-4%'))

See attachement.

Lionel do you confirm that it works in your case ?

John, so now I have 2 solutions in the LOAD script and in the formula which one will be the best in term of performance and maintenance ?

[johnw]

gchampion wrote:John, so now I have 2 solutions in the LOAD script and in the formula which one will be the best in term of performance and maintenance ?

Well, a load script solution will always outperform a chart equivalent since it only has to calculate once per load instead of once every time someone looks at the chart. However, load solutions may also be less flexible, in that they won't be sensitive to selections, and won't be sensitive to any dimensions other than the ones you chose at the time of the load.

I do my aggregation in charts for those reasons. As long as it performs adequately for your needs, I would use the chart expression.

[#7]

Yes, it does work, thanks a lot !

To understand the solution, what are the respective impacts of both aggr functions and TOTAL and NODISTINCT keywords in this formula?

Thanks !

Lionel

[#8]

Hello. I will like to ask how do i replace "aggr", if i want to perform decile analysis in the "load script" instead?