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]

How about the below:

Option I:

  (Sum

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

          and

          (Sum(AGGR({<Inside_Sales=>}Sales_Qty_SP, Short_ID, Sales_Num, Inside_Sales)>0)),

    (Sales_Price_SP-Sales_Cost_SP))))

Option II:

  (Sum

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

          and

          (AGGR(Sum({<Inside_Sales=>}Sales_Qty_SP), Short_ID, Sales_Num, Inside_Sales)>0),

    (Sales_Price_SP-Sales_Cost_SP))))

Also look into this link:  Set Analysis in the Aggr function

Note: Please check the brackets are correct.

[laureadiaz]

In order for "Expression OK" to show up I got:

=
(IF((SUM(AGGR(Sales_Price_SP, Short_ID, Sales_Num, Inside_Sales)>0))
and (SUM(AGGR(Sales_Qty_SP, Short_ID, Sales_Num, Inside_Sales)>0)),
(Sales_Price_SP-Sales_Cost_SP)))

Starting with Sum, I could not get to work. But when I applied no data appeared.

Same true for:

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

[laureadiaz]

I'm afraid I cannot share a file, nor can I read a file if someone else shares it on this forum unfortunately.

But I am hoping that the expression with the set analysis will remain the same no matter what Inside_Sales selection I make to compare to the equation without set analysis.

Does that make sense?

[vishsaggi]

You can mask the data if there is any confidential information using following steps

Preparing examples for Upload - Reduction and Data Scrambling

We will not upload any files, we just work on your file and if expr works we will just post the expr that gives us the results. This way it will be easy to work on.

[trdandamudi]

Did you give a try on the Option II above ?

[laureadiaz]

=
Sum(
(IF((AGGR(SUM(Sales_Price_SP, Short_ID, Sales_Num, Inside_Sales, Month, Year)>0))
and (AGGR(SUM(Sales_Qty_SP, Short_ID, Sales_Num, Inside_Sales, Month, Year)>0)),
(Sales_Price_SP-Sales_Cost_SP))))

Has "error in expression", and you can see the final difference is not loading with no color..

[trdandamudi]

If possible can you share a sample .qvw with data. So that I can try it out and send you the expression.

[laureadiaz]

I have a smaller scrambled version but am not sure how to attach it here?

And I am never able to open the examples people post here, any tips?

[laureadiaz]

I found under create, upload a file, but when I was done the error read "You are not allowed to create or update this content"

Maybe my account is missing something?

[laureadiaz]

Maybe this worked?

https://community.qlik.com/docs/DOC-20363