topic Re: Set Analysis in QlikView

Set Analysis

qlikie1 — Fri, 13 Aug 2021 12:24:01 GMT

Hello everybody,

I have set analysis within a chart that has below syntax:

count(distinct {
<Primary_Product_Flag={1},

Target_Product=,Sales={"=sum(Sales)>0"},

Product_Code={"=sum({<Product_Group={'PS0016'},Key_IOL_Name >}Sales)>0"}
>
}
Product_Code)

Could you please help me to understand what Key_IOL_Name do at this point?

Re: Set Analysis

francisco_orte — Fri, 13 Aug 2021 12:41:46 GMT

Hi @qlikie1

A field without any set afterwards, prevents expression to be affected on selections on that field. In other words that sum is not affected on selections made by users in Key_IOL_Name.

You can also write Key_IOL_Name= (with an equal symbol at the end) with same behavior

Hope it helps

Regards

Re: Set Analysis

qlikie1 — Mon, 16 Aug 2021 10:02:34 GMT

Hi @francisco_orte,

Thanks for your response. Could you please help me to understand the formula:
it filters Primary_Product_Flag = 1, ignores Target_Product filter, took Sales>0 for each Product_Code and
took all the Product Codes taht has Sales >0 for Product_Group={'PS0016'} ignoring Key_IOL_Name filter?

Re: Set Analysis

francisco_orte — Mon, 16 Aug 2021 10:26:54 GMT

Hi @qlikie1

I think a good aproach that may help you to understand is seeing set analysis as a selection for each set modifier.

In your example

Filters Primary_Product_Flag with 1, so forget rest of data (be carefull, alse null values!!!)

Avoid any Target_Product filtering made by user.

Filters on Sales!!!! (not on Product_Code), taking Sales >0. It seems is not intended calculation, you can simplify by Sales ={">0"}

Filters Product_Code (now it is its moment 😉 ), taking those products with Sales > 0 for Product_Group PS0016 whatever Key_IOL_Name is selected

And remember, al Set expressions are evaluated out of any object context

Hope it helps

Regards

Re: Set Analysis

qlikie1 — Mon, 16 Aug 2021 13:03:46 GMT

Thanks @francisco_orte . Could you please be more specific about: And remember, al Set expressions are evaluated out of any object context.

Does this mean that no matter how many other modifiers you have, what is in set expression goes first priority omitting other conditions?

Re: Set Analysis

francisco_orte — Mon, 16 Aug 2021 14:02:36 GMT

Hi @qlikie1

It means that it works as when you do same selections on your dashboard. You can have a "base Expression" with no set analysis, and you can get same result just making same selections as expressed on your formula.

Regards

Re: Set Analysis

NageshSG — Mon, 16 Aug 2021 15:27:16 GMT

Hi,

In addition to above points just noticed following in the set expr on below part..

Product_Code={"=sum({<Product_Group={'PS0016'},Key_IOL_Name >}Sales)>0"}

Are you missing the "P" element above? Not sure if the following is the expected condition:

Product_Code=P{1<Product_Group={'PS0016'},Sales={'>0'}>} Key_IOL_Name >}

Regards.

Re: Set Analysis

francisco_orte — Mon, 16 Aug 2021 16:15:40 GMT

HI @NageshSG

both expressions are different. On the original one (no p function) you take all products with TOTAL sum sales positive, on the other side (with P) you get all products with at least one positive Sale.

In some scenarios results could be very different. Also be aware on 1 , cause you are missing all user selections.

Regards

Re: Set Analysis

NageshSG — Tue, 17 Aug 2021 04:51:09 GMT

Hi @francisco_orte

Thanks for highlighting the differences.

Please correct me if my understanding is incorrect:

1. Using identifier 1 inside a P() element, should work like a 'sub query'. In the example above, the question answered is:

1.1 Get me the list of customers (Key_IOL_Name) who bought items having Product_Group = PS0016 (irrespective of other user selection statuses, since 1 identifier is used). Once this is evaluated then...

1.2 The element function P( ) here returns a list of possible Key_IOL_Name;
those that are implied by the selection ‘PS0016’ in the field Product_Group.
The list of Key_IOL_Name is then used as a selection in the field Product_Code.

1.2 The value returned for Product_Code above is therefore those codes which are bought by Key_IOL_Names. The outer $ identifier still remains and therefore the user selection is still respected for outer conditions.

Regards.

Re: Set Analysis

qlikie1 — Tue, 17 Aug 2021 08:48:29 GMT

Hi @francisco_orte , Thanks for your response,

and one more thing, even though we have Target_Product= in formula, the selections on target product work anyway, unless I remove this part -- Product_Code={"=sum({<Product_Group={'PS0016'},Key_IOL_Name >}Sales)>0" from the formula. Is it supposed to work like that?

Re: Set Analysis

francisco_orte — Tue, 17 Aug 2021 09:31:32 GMT

Hi @NageshSG

I highly recommend you to review again all documentation related to set analysis

https://help.qlik.com/en-US/qlikview/May2021/Subsystems/Client/Content/QV_QlikView/ChartFunctions/SetAnalysis/set-analysis-expressions.htm

May be your expressions works as you are explaining, but it's a different expression, with different results of that proposed by @qlikie1

In our first scenario, we only intend to avoid selections on Key_IOL_Name

Hope it helps

Regards

Re: Set Analysis

francisco_orte — Tue, 17 Aug 2021 09:33:33 GMT

Hi @qlikie1

Just try to add Target_Product= on the set analysis you are using for Product_Code. In that case Target_Product selections will not affect to Product_Code.

Hope it helps

Regards

Re: Set Analysis

NageshSG — Tue, 17 Aug 2021 13:45:11 GMT

Hi @francisco_orte

Thanks for your advise and the links.

Learning is a continuous process for me.

Regards

Re: Set Analysis

qlikie1 — Tue, 17 Aug 2021 15:05:10 GMT

@francisco_orte Thank you very much for prompt support and detailed explanations! You helped me a lot!