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/

[trdandamudi]

Can you be little specific and share a sample app please...

[laureadiaz]

woops just realized I copied the same expression twice above. I cannot add an app sorry ( I also can't open any that people post her :,(

=
(Sum(
(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)))))

does work.

but if I want to not let Inside_Sales be selectable via set analysis ({$<Inside_Sales=>} it does not work.

Maybe an ONLY statement will work but I am not sure, as the developer book and examples on here do not seem to match?!?

[trdandamudi]

Give a try with the below code:

=
(Sum
({1<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)))))

Note: I replaced $ with 1.

[laureadiaz]

It is still not quite right, I am expecting the set analysis to stay the same as if Inside_Sales was not selected but keep other selections. with the 1 it is also not allowing other fields to not be selected as well.

Let's say if without Inside_Sales being selected the answer was 4.

Once I select something from inside sales, I would expect the one with the set analysis restriction to stay 4, and the other without the 1 or $ to reduce to 3 or 2, because I am drilling down.

[trdandamudi]

I think the below link will help you:

Ignore all selections except some specific fields using Set Analysis

[laureadiaz]

I did read this one, which is how I cam up with:

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

The sad thing is if I get rid of the IF statement it works as I would want, but I need the IF to clean up data...

[vishsaggi]

Can you share a sample file you are working on and your expected output you are trying to achieve?

[trdandamudi]

Give a try on the below code:

(Sum

  ({<Inside_Sales=>}
  (IF((AGGR({<Inside_Sales=>} Sales_Price_SP, Short_ID, Sales_Num, Inside_Sales)>0)

          and

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

[laureadiaz]

When adding the set analysis after AGGR, the equation is no longer loading properly, the top reads Expression OK, but the color coding stops at the first AGGR, like it's not really loading.