Bottom sum

[andrespa]

Hi everyone,

I have a little problem that I can't figure out the solution. I have a straight table like shown below:

I have check the Max number option to 15 in the table, so the total of the Net Margin column (11.680,779,7) is not what I want to show in the title.

What I want to do is to sum the first 15 values of the Net Margin column and put them into the title of the expression. Currently I have tried all these expressions:

=Bottom(sum({<TypeFact={'Sales'}>} P_Margin_net*$(vCurrencyRate)))  // this one give me the total of all rows in the Net Margin column

=sum(aggr(if(rank(aggr(rangesum(sum({<TypeFact={'Sales'}>} P_Margin_net*$(vCurrencyRate))),Customer_number),4)<=15,

aggr(rangesum(sum({<TypeFact={'Sales'}>} P_Margin_net*$(vCurrencyRate))),Customer_name)),Customer_number))

Hope I was clear enough.

Thanks,

Andrés

[MayilVahanan]

Hi

Try like this

=Sum(Aggr(If(Rank(sum({<TypeFact={'Sales'}>} P_Margin_net*$(vCurrencyRate)) ) <=15,

          Sum({<TypeFact={'Sales'}>} P_Margin_net*$(vCurrencyRate))

), Customer_number, Customer_name))

[andrespa]

Hi Mayil, sorry it shows me the same result as before. Not what I lookinf for.

Thanks anyway.

[MayilVahanan]

HI

Can you provide a sample file

[Gysbert_Wassenaar]

Try this one:

sum({<TypeFact={'Sales'}, Customer_number={"=rank(sum({<TypeFact={'Sales'}>} P_Margin_net))<=15"}>} P_Margin_net*$(vCurrencyRate))

[andrespa]

Hi Gysbert, that was close. It shows me the first 15 margin values, but what I want is the last 15. In the chart I'm able to do that by sorting descending by numeric value. But on the text object I can't do that. Any ideas of how to achieve that?

Cheers,

Andrés

[jonathandienst · Accepted solution]

I think just flip the sign of the sum in gysbert's expression to get the last 15 values:

sum({<TypeFact={'Sales'}, Customer_number={"=rank(-sum({<TypeFact={'Sales'}>} P_Margin_net))<=15"}>} P_Margin_net*$(vCurrencyRate))

[andrespa]

It worked! Thanks a lot!