It's not clear that you'll get any measurable benefit from set analysis, because I don't know what's in your variables. Where you get into performance trouble is if you do something like this:
sum(if(A='B',X))
Think of the sum() as a loop in a procedural language. For each row, if A = 'B', then add X to the running total. So QlikView has to process ALL rows to get the result, doing the if() on each. For performance, you want to do this instead:
sum({<A*={'B'}>} X)
Internally, that basically "selects" B as the value of A, and so QlikView only has to process the matching rows, and doesn't have to check a condition for each. So no matter how many rows match, it should be faster, and the fewer rows that match, the faster it should be in comparison to the sum(if()).
However, if you're doing this:
if(sum(A)=10,X)
There there's nothing to convert to set analysis, and performance isn't really going to get any better. So in conclusion, if you're doing a sum(if()), convert it to set analysis or preferably look for a data model solution. If you're doing an if(sum()), you're fine.
Based on the understanding your shared, since I have to compare IF(several sum()) conditions, it means I am fine with my current format of expression? And I should not worry about Set analysis?
I am asking this to make sure that I am not doing something odd.
Again, without seeing what's in your variables, I don't actually know what your expression is or does. But based on what I CAN see, set analysis isn't even applicable, and your current approach should be fine.
I dont see any one more experienced than you on this comunity, that's why asking directly from you.
I have attached a qvw file in which there are two table charts. Both of them should have same output. For one table expression is created with IF logic and for the other one with SET logic.
I am sure that results with IF are correct with my source.
Can you identify what is the problem with SET logic, why when I select " >= " it does not give correct output/number or records.
Or alternatively can you please write a SET expression for me which will give the same out as IF logic does?
Sum(Value={">=0"} Value) means sum all positive values. It's the equivalent of sum(if(Value>=0,Value)), NOT if(sum(Value)>=0,sum(Value)). Set analysis is like making selections. You don't make a selection in field sum(Value), just in field Value.
Even if you COULD do this with set analysis (and I'm not quickly thinking of a way), it wouldn't likely improve performance. It's the sum(if(...)) approach that can be made much faster, not the if(sum(),...) approach. Was it a performance problem that led you to try to convert to set analysis?
This is almost an aside, but although set analysis is a wonderful tool in QlikView toolbox, I consider it an overused tool by most developers. Set analysis often isn't the cleanest and highest-performance way to solve problems. If I can do something with data model changes instead of set analysis, that tends to be my preference. So I try to avoid set analysis where practical. That's not to say I don't use it - I use it all the time. But I kind of consider it a personal failing when I'm driven to use it. I always feel like if only I was a little smarter, I could have figured out a better way. And certainly sum(if(...)) is NOT a better way - it's worse than set analysis. But I see nothing wrong with an if(sum()...).
No. If(sum()) and sum(if()) have completely different meanings.
Data:
Something, Value
A, -10
A, 5
B, -5
B, 10
Chart Definition:
Dimension = Something
Expression 1 = if(sum(Value)>=0,sum(Value))
Expression 2 = sum(if(Value>=0,Value))
Result:
Something, Expression 1, Expression 2
A, null, 5
B, 5, 10
Hopefully you can work out why that's the case when looking at very simple data. This is also why your set analysis doesn't work. And switching to sum(if()) will likely perform worse, not better.
