Set analysis with AGGR If statement

[laureadiaz]

I am trying to limit selections in an expression that has AGGR. I want to have in a graph what the lack of selection is for comparison.

This one is working:

=
(Sum
({$<Inside_Sales=>}
(IF((AGGR(Sales_Price_SP, Short_ID, Sales_Num, Inside_Sales)>0)
and (AGGR(Sales_Qty_SP, Short_ID, Sales_Num, Inside_Sales)>0),
(Sales_Price_SP-Sales_Cost_SP)))))

This one is not ignoring the selection for comparison

=
(Sum
({$<Inside_Sales=>}
(IF((AGGR(Sales_Price_SP, Short_ID, Sales_Num, Inside_Sales)>0)
and (AGGR(Sales_Qty_SP, Short_ID, Sales_Num, Inside_Sales)>0),
(Sales_Price_SP-Sales_Cost_SP)))))

I have a feeling it has to do with the AGGR but not sure.


Thanks in advance for the help/

[sunny_talwar]

Try this may be

=Sum({$<Inside_Sales=>} Aggr(If(Only({$<Inside_Sales=>}Sales_Price_SP) > 0 and Only({$<Inside_Sales=>} Sales_Qty_SP) > 0, Only({$<Inside_Sales=>} Sales_Price_SP - Sales_Cost_SP)), Short_ID, Sales_Num, Inside_Sales))

[sunny_talwar · Accepted solution]

For your sample attached... try this

=Sum({$<Inside_Sales=>} Aggr(If(Only({$<Inside_Sales=>}Sales_Price_SP) > 0 and Only({$<Inside_Sales=>} Sales_Qty_SP) > 0, Only({$<Inside_Sales=>} Sales_Price_SP - Sales_Cost_SP)), Short_ID, Sales_Num, Month, Year))

[laureadiaz]

That seems to have worked, I thought I might need an "ONLY" in there somewhere.

Thanks!