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!

[sunny_talwar]

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

[Chanty4u]

.if use  iteration

set analsys  wit give direct result

u can chk here

RE:Diference-Perfomance

[#4]

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

[#5]

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.

[sunny_talwar]

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.

[swuehl · Accepted solution]

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

[#8]

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.

[sunny_talwar]

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

[#10]

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.