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!

[johnw]

I really need to commit more of the technical details to memory. I've read these before, but clearly didn't entirely internalize them. From "The Calculation Engine":

"First it finds combinations of the values of the fields used in the aggregation function, and, if necessary, builds a temporary look-up table... Flags are often used inside aggregation functions, and usually this is not a problem. However, be aware that QlikView will create all combinations of the two fields before summing, and that this could in odd cases cause duplication of records."

So when it created all combinations for our flag, that's when the duplication appeared. Got it. Thank you.

[Anonymous]

just another example:

LOAD * INLINE [

Key, A

1, 10

1, 40

2, 40

];

LOAD * INLINE [

Key, B

1, 2

1, 3

];

= 10 + 40 + 40

= 2 + 3

= (10 + 40 + 40) * (2 + 3)

= (10 * 2 + 10 * 3 + 40 * 2 + 40 * 3 + 40 * "NULL" )

So, you see, it's very easy to run into problems, if you don't know exactly how your data is structured...

[#4]

For those who are interested in this topic, this link have a good explanation with visualization of tables, results and joins where the duplications occur.

Using “IF” in expressions vs using “Set Analysis” | QlikView Help, Tips & Hints