topic Re: Set analysis with AGGR If statement in QlikView

Set analysis with AGGR If statement

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

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/

Re: Set analysis with AGGR If statement

trdandamudi — Wed, 22 Aug 2018 15:28:40 GMT

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

Re: Set analysis with AGGR If statement

laureadiaz — Wed, 22 Aug 2018 15:38:49 GMT

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?!?

Re: Set analysis with AGGR If statement

trdandamudi — Wed, 22 Aug 2018 15:47:08 GMT

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.

Re: Set analysis with AGGR If statement

laureadiaz — Wed, 22 Aug 2018 15:58:58 GMT

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.

Re: Set analysis with AGGR If statement

trdandamudi — Wed, 22 Aug 2018 16:15:55 GMT

I think the below link will help you:

Ignore all selections except some specific fields using Set Analysis

Re: Set analysis with AGGR If statement

laureadiaz — Wed, 22 Aug 2018 16:24:35 GMT

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...

Re: Set analysis with AGGR If statement

vishsaggi — Wed, 22 Aug 2018 18:54:14 GMT

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

Re: Set analysis with AGGR If statement

trdandamudi — Wed, 22 Aug 2018 23:40:59 GMT

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

Re: Set analysis with AGGR If statement

laureadiaz — Thu, 23 Aug 2018 14:17:14 GMT

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.

Re: Set analysis with AGGR If statement

trdandamudi — Thu, 23 Aug 2018 14:30:38 GMT

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.

Re: Set analysis with AGGR If statement

laureadiaz — Thu, 23 Aug 2018 15:04:14 GMT

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

Re: Set analysis with AGGR If statement

laureadiaz — Thu, 23 Aug 2018 15:17:07 GMT

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?

Re: Set analysis with AGGR If statement

vishsaggi — Thu, 23 Aug 2018 15:27:00 GMT

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.

Re: Set analysis with AGGR If statement

trdandamudi — Thu, 23 Aug 2018 17:45:25 GMT

Did you give a try on the Option II above ?

Re: Set analysis with AGGR If statement

laureadiaz — Thu, 23 Aug 2018 18:06:40 GMT

=
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..

Re: Set analysis with AGGR If statement

trdandamudi — Thu, 23 Aug 2018 19:32:54 GMT

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

Re: Set analysis with AGGR If statement

laureadiaz — Fri, 24 Aug 2018 14:14:43 GMT

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?

Re: Set analysis with AGGR If statement

laureadiaz — Fri, 24 Aug 2018 14:29:43 GMT

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?

Re: Set analysis with AGGR If statement

laureadiaz — Fri, 24 Aug 2018 14:39:46 GMT

Maybe this worked?

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