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

[#1]

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!

[swuehl]

LOAD * INLINE [

Key, Value

1, 100

];

LOAD * INLINE [

Key, Flag

1, Blue

1, Blue

];

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

vs.

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

[sunny_talwar]

I see it now

[#4]

Perfect!

Thanks!

[#5]

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

];

or

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

1, Blue

2, Red

];

The duplication happens again!

[swuehl]

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

[#7]

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.

[swuehl]

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

[#9]

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.

[johnw]

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.

[swuehl]

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

1) Logical inference

2) Aggregation engine

See also

Logical Inference and Aggregations

The Calculation Engine

w.r.t. André's latest sample: Do you need any more explanation here?

I believe using a key value in the aggregation (example with if() ) is similar to performing a Count(KeyField), the results are not deterministic (or to use different view: It's not clear for QV how to perform the JOIN / Lookup (so one could say: Well, it's clear, use the records from the same table as Value, which should be the minimal number of records. I believe for QV it's not clear))