topic Re: Difference between conditional sum with 'if' and with set analysis in QlikView

Difference between conditional sum with 'if' and with set analysis

Wed, 25 Nov 2020 16:16:04 GMT

Hello,

What is the difference between doing a conditional sum with "if" and with set analysis?

Example:

In a bar chart I want to show a sum of the values (field: value) associated with men (field: sex). If I use "if" inside "sum", the value is different then using set analysis expression:

sum(if ([sex] = 'M', [value], 0));

sum({$<[sex]={'M'}>} [value]);

I know that the performance of set analysis is better. The thing is that the result of the expressions are different (a big difference). I guess there are some problems with my model, but anyway I thought that those expressions would be equivalent.

In other words, what is the difference between this two expression (besides performance)? or How can the results be different?

Thanks!

Re: Difference between conditional sum with 'if' and with set analysis

sunny_talwar — Fri, 15 Apr 2016 17:17:47 GMT

I don't think there should be any difference, but can you share an example where you are seeing different values?

Re: Difference between conditional sum with 'if' and with set analysis

Chanty4u — Fri, 15 Apr 2016 17:21:36 GMT

.if use iteration

set analsys wit give direct result

u can chk here

RE:Diference-Perfomance

Re: Difference between conditional sum with 'if' and with set analysis

Mon, 18 Apr 2016 12:21:32 GMT

The image below shows two text box with the expression used on the caption title. As anyone can see the results are different.

Re: Difference between conditional sum with 'if' and with set analysis

Mon, 18 Apr 2016 12:28:01 GMT

Thanks Chanty 4u,

The link that you provided guided me to this question which is still not answered:

Difference between IF condition and Set analysis

In there perumal_41 said that "if Condition Some times return Duplicate value and consume more memory" but I couldn't find why or when "sometimes return duplicates". I think it is important to know the mechanics behind this to avoid showing wrong values.

The performance issues I understand and it is not the point with my question.

Re: Difference between conditional sum with 'if' and with set analysis

sunny_talwar — Mon, 18 Apr 2016 14:41:51 GMT

Have you made selections in [Origem ....] field? That might cause differences as the set analysis will always include RGPS regardless of any selection in [Origem...] field, where as if will filter out the data.

Re: Difference between conditional sum with 'if' and with set analysis

swuehl — Mon, 18 Apr 2016 14:56:26 GMT

How are the two fields for value and origing related in your data model? Do they reside in the same or different tables?

The if() statement might force QV to create a temporary join between tables and this might lead to fact (value) duplication. All depends on your data model...

Re: Difference between conditional sum with 'if' and with set analysis

Mon, 18 Apr 2016 16:57:23 GMT

In the example there is no direct selection of [Origem ....]. But there is an indirect selection. If I clear all the selections the values are still different.

In that state, if I select directely the value 'RGPS' for that field, the values did not change.

If I select a different value for that field then the sum with "if" is 0 (which is expected since no value associated with 'RGPS' is possible) and the sum with set analysis did not change. This corroborates with what you told that set analysis will always include RGPS regardless of any selection in that field.

And actually this is one difference between the two expressions!

But still no makes sense when there is no selection in that field. I tried selections in other fields associated with these values and they change the value of both expressions.

Re: Difference between conditional sum with 'if' and with set analysis

sunny_talwar — Mon, 18 Apr 2016 17:07:41 GMT

Would you be able to share a sample where you can show this stuff going on?

Re: Difference between conditional sum with 'if' and with set analysis

Mon, 18 Apr 2016 17:11:30 GMT

I think you are in the right direction. I believe there is some problem with my data model, but I still did not find. The value returned from the expression with set analysis seems the right result. Duplication of some values is the best shot!

The field "Origem..." came from the same table of the value calculated. But also the same field is used as an association (synthetic key, I know that I shall to remodel and remove the synthetic keys) with another table.

The thing is that the two expressions are not equivalent!

swuehl, can you provide an example when the if statement might duplicate the value? Just to understand that.

Re: Difference between conditional sum with 'if' and with set analysis

swuehl — Mon, 18 Apr 2016 17:13:23 GMT

LOAD * INLINE [

Key, Value

1, 100

];

LOAD * INLINE [

Key, Flag

1, Blue

];

=Sum(If(Flag='Blue', Value))

vs.

=Sum({<Flag = {Blue}>} Value)

Re: Difference between conditional sum with 'if' and with set analysis

sunny_talwar — Mon, 18 Apr 2016 17:18:09 GMT

I see it now

Re: Difference between conditional sum with 'if' and with set analysis

Mon, 18 Apr 2016 17:22:40 GMT

Perfect!

Thanks!

Re: Difference between conditional sum with 'if' and with set analysis

Mon, 18 Apr 2016 17:50:10 GMT

swehl,

Do you understand why, if I add a new line to the first table of your example:

LOAD * INLINE [

Key, Value

1, 100

1, 25

];

LOAD * INLINE [

Key, Value

1, 100

2, 25

];

(without any changes to the second table) The duplication does not occur! I think I did not understand the "joins" behind these scenarios.

But, in the same scenario with the tuple (2, 25), if I add a new line on the second table (2, Red) like below:

LOAD * INLINE [

Key, Value

1, 100

2, 25

];

LOAD * INLINE [

Key, Flag

1, Blue

2, Red

];

The duplication happens again!

Re: Difference between conditional sum with 'if' and with set analysis

swuehl — Mon, 18 Apr 2016 18:06:26 GMT

I still get Value duplication with your first two tables. Could you share your sample?

Re: Difference between conditional sum with 'if' and with set analysis

Mon, 18 Apr 2016 18:36:12 GMT

Sure,

I attached the qvw with three examples: the one that you showed at first (with duplication) and two other which the duplication is not shown.

Re: Difference between conditional sum with 'if' and with set analysis

swuehl — Mon, 18 Apr 2016 18:43:56 GMT

Ah, ok, you are doing the comparison / set analysis on the key field instead of flag field.

Re: Difference between conditional sum with 'if' and with set analysis

Mon, 18 Apr 2016 18:55:05 GMT

Right, my mistake.

I was adapting your example to my context which the conditional field is the key of the relationship.

but in this case (when using the key in the conditional expression), the behavior of Qlikview seems awkward to me.

Re: Difference between conditional sum with 'if' and with set analysis

johnw — Mon, 18 Apr 2016 19:16:37 GMT

So just to see if I understand what we think is going on, with the sum(if()), for every row in the field with the flag, it takes the key and looks up the value. Or equivalently, it does a join of the two tables, duplicating the value. So we get 200. With the second, it "selects" Blue, which means that the only possible value of key is 1, and therefore the only possible value of value is 100, so it returns 100. This is all roughly speaking.

Re: Difference between conditional sum with 'if' and with set analysis

swuehl — Mon, 18 Apr 2016 19:49:10 GMT

Right, I believe that's the basic working of QlikView aggregations, isn't it?

1) Logical inference

2) Aggregation engine