topic Re: Aggr with sumif function in QlikView

Aggr with sumif function

snehamahaveer — Wed, 25 Nov 2020 16:16:04 GMT

Hi Everyone,

I was creating a report where I have to first calculate the orders where margins are <45%(for eg) and then sum the sales that come from it.

I am currently using an aggr function with an if and then sum the values, however it seems to not be working.

=Sum( if(aggr((sum(SALES)-sum(COST))/sum(SALES),ORDER_NO)<0.45, sum(SALES)))

This formula works when a single order is selected and not when a list of orders are under this category.

=if(aggr((sum(SALES)-sum(COST))/sum(SALES),ORDER_NO)<0.45, ORDER_NO) lists all the orders where margins are less than 45% (for eg)

Can someone help me resolve this issue, as its pretty urgent.

Thanks in advance.

Re: Aggr with sumif function

sunny_talwar — Wed, 03 Aug 2016 10:24:10 GMT

Can you try this:

=Sum(Aggr(If((Sum(SALES) - Sum(COST))/Sum(SALES) < 0.45, SALES), ORDER_NO))

Re: Aggr with sumif function

snehamahaveer — Wed, 03 Aug 2016 10:37:00 GMT

Thank you Sunny, that seems to have fixed my issue!

Awesome. Much appreciated

Re: Aggr with sumif function

sunny_talwar — Wed, 03 Aug 2016 10:37:55 GMT

No problem at all, I am glad I was able to help you quickly here

Re: Aggr with sumif function

sunny_talwar — Wed, 03 Aug 2016 10:38:55 GMT

You might also want to try this:

=Sum({<ORDER_NO = {"=(Sum(SALES) - Sum(COST))/Sum(SALES) < 0.45"}>} SALES)

Re: Aggr with sumif function

snehamahaveer — Wed, 03 Aug 2016 10:49:49 GMT

True, that seems to work too.! I was under the impression that I cannot use Formulas in set analysis, how wrong I was.

Thanks for your help @Sunny T

Re: Aggr with sumif function

sunny_talwar — Wed, 03 Aug 2016 10:52:10 GMT

You can, but only if those formulas are associated with a unique field (in your case it was ORDER_NO). If for some reason you had to check the margin based on ORDER_NO and Month, then set analysis won't work and you will have to go back to Aggr() function.

Best,

Sunny