Aggr with sumif function

[snehamahaveer]

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.

[sunny_talwar · Accepted solution]

Can you try this:

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

[snehamahaveer]

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

Awesome. Much appreciated

[sunny_talwar]

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

[sunny_talwar]

You might also want to try this:

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

[snehamahaveer]

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

[sunny_talwar]

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