topic Re: Bottom sum in QlikView

Bottom sum

andrespa — Wed, 10 Feb 2016 08:33:53 GMT

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

Re: Bottom sum

MayilVahanan — Wed, 10 Feb 2016 11:06:03 GMT

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

Re: Bottom sum

andrespa — Wed, 10 Feb 2016 11:35:18 GMT

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

Thanks anyway.

Re: Bottom sum

MayilVahanan — Wed, 10 Feb 2016 11:36:38 GMT

Can you provide a sample file

Re: Bottom sum

Gysbert_Wassenaar — Sat, 13 Feb 2016 15:17:51 GMT

Try this one:

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

Re: Bottom sum

andrespa — Mon, 15 Feb 2016 11:26:41 GMT

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

Re: Bottom sum

jonathandienst — Mon, 15 Feb 2016 12:03:50 GMT

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

Re: Bottom sum

andrespa — Mon, 15 Feb 2016 12:21:44 GMT

It worked! Thanks a lot!