Part of the revenue coming from the top 1% of the customers (top1% in volume)

[#1]

Hi,

This is the data:

FiscalYear	Business	Customer	Income
1213	A	A	1$
1213	A	B	2$
1314	A	A	4$
1314	A	B	3$
1415	A	A	5$
1415	A could be B....	B	6$

I would like in a straight table with:

Formula		1213	1314	1415
=count(distinct Customer)	Number of customer	300	350	400
=round(count(distinct Customer)/100)	Top 1% of customer in volume	3	4	4
=sum({<Customer={"=Aggr(Rank(Sum({<Business={'A'}>}Income),1,1),Group)<= TOP1% "},Business={'A'}>}Income)/sum({<Business={'A'}>}Income)	Part of the total revenue coming from the top 1% customers (i.e Revenue with rank max = count( distinct customer)/100)

My issue is to take the top 1%. I made a variable like this:     vRankmax: round(count(distinct Customer)/100)   and used   $(vRankmax) in the formula, but it's not working. Can you help me please? Thanks.

Best regards,

Ludo

[#2]

Little mistake: in the table it's

=sum({<Customer={"=Aggr(Rank(Sum({<Business={'A'}>}Income),1,1),Customer)<= TOP1% "},Business={'A'}>}Income)/sum({<Business={'A'}>}Income)

[sunny_talwar]

Would be able to share a sample to take a look at the issue?

[swuehl]

I would start with something like this (assuming vRankMax is defined with a leading equal sign and evaluated to a number):

=Sum(

      Aggr(

          If( Rank( Sum(Income)) <= vRankMax, Sum(Income)),

          Customer, Business, FiscalYear

          )

)

Adapt the aggr() dimensions to the required granularity for the rank.

Then you should be able to use above expression e.g. in a chart with dimensions Business and FiscalYear.

If you want to use a set expression, you would need to create and define keys with the required granularity for the field modifier with the ranking search,Customer alone is probably not granular enough.

[#5]

Hi Swuehl,

This doesn't work because the only dimension in the straight table has to be: Fiscal Year only.

Then, I have multiples straight tables in which I will manually modify the dimension: business.

The result consist in a kind of map of all the different business, represented by one straight table for each.

-------

In your formula, how do I look at the results to one particular business for example?

Best regards,

Ludo

[swuehl]

Maybe like this for business A

=Sum({<Business={'A'}>}

      Aggr(

          If( Rank( Sum({<Business={'A'}>} Income)) <= vRankMax, Sum({<Business={'A'}>}Income)),

          Customer, Business, FiscalYear

          )

)

or maybe

=Sum({<Business={'A'}>}

      Aggr(

          If( Rank( Sum({<Business={'A'}>} Income)) <= vRankMax, Sum({<Business={'A'}>}Income)),

          Customer, FiscalYear

          )

)

If your chart dimensions are less granular than the aggr() dimensions, the results of the aggr() function will be projected to the chart dimensions, using the outer aggregation function ( Sum() ), i.e. all Customer Income above threshold per Businees A and per fiscal year will be grouped by fiscal year and summed.