Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

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!

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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

View solution in original post

22 Replies
sunny_talwar

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

Chanty4u
Champion III
Champion III

.if use  iteration

set analsys  wit give direct result

u can chk here

RE:Diference-Perfomance

Not applicable
Author

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

diferenca_sum.png

Not applicable
Author

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

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

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

View solution in original post

Not applicable
Author

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?

Not applicable
Author

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.