5 Replies Latest reply: Jun 13, 2016 5:10 AM by Stefan Wühl

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

Hi,

This is the data:

1213

A

A

1\$

1213

AB2\$
1314AA4\$
1314AB3\$
1415AA5\$
1415A could be B....B6\$

I would like in a straight table with:

Formula121313141415
=count(distinct Customer)Number of customer300350400
=round(count(distinct Customer)/100)Top 1% of customer in volume344
=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

• ###### Re: Part of the revenue coming from the top 1% of the customers (top1% in volume)

Little mistake: in the table it's

• ###### Re: Part of the revenue coming from the top 1% of the customers (top1% in volume)

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

• ###### Re: Part of the revenue coming from the top 1% of the customers (top1% in volume)

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

)

)

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.

• ###### Re: Part of the revenue coming from the top 1% of the customers (top1% in volume)

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

• ###### Re: Part of the revenue coming from the top 1% of the customers (top1% in volume)

Maybe like this for business A

Aggr(

)

)

or maybe

Aggr(