Uses of SUM(If(...)) and If(...,SUM()), differences between QV7 and QV9

[#1]

Hi,

I submit to your attention this document, that give different numbers depend on the version of QlikView used :

in version 7, the first blue expression gives the same result as the second orange one

in version 9 (or 8.5), the two expressions give different results

Can somebody explain to me why ?

Thanks in advance,

Eric.

[fernandotoledo]

I´ve been told that there are some restrictions in expressions syntax. Some expressions that were allowed in 7 gives a error in 8.5/9/10. Others gives a strange result because how QV interpret it.

There are some clues about this "WAD" issues in the What´s new documentation since version 8.5.

p.s.: "WAD" = 'W'orking 'A's 'D'esigned

[#3]

I searched a little in the "What's new" of past version of QV (8 & 8.5), but i didn't see anything about this... Have you got references of the document you told ?

[Oleg_Troyansky]

SInce QlikView 7.52, there were a number of developments in the internal structure of the way expressions are calculated. Not all of those changes made it into "What's new" documents. Sometimes, they might be mentioned in the form of "improvements have been made in the performance of Chart expressions" or something like this.

Speaking of the problem at hand, - I think the focus should be on the logical differences between IF( SUM ) versus sum( IF ). In your example, the expression IF (Field1 = ..., ) is incorrectly formulated, because multiple values of Field1 are available, and the answer to the question can be"true" or "false" at the same time.

The only time IF ( SUM ) structure can be valid, is when the condition can return a single answer (true or false), not matter what selections were made. In all other cases, sum (IF ) needs to be used, because then the condition is being evaluated at each row, and the result is always unique.

I'd say, if it used to work in ver. 7.52 - it was working by a fluke, because logically speaking, it shouldn't be valid at all...

[#5]

Not entirely related, but I find using the SUM(IF) option to be extremely taxing on the memoryspace; especially if you're dealing with large datasets. Whenever I'm going to do a conditional summary, I always use set analysis. The results render faster, and take up less memory space.

[Oleg_Troyansky]

Fry,

totally agree with you! Those folks who followed this forum long enough, have seen my on-going war with the IF() conditions. Set Analysis, when possible, is a much better alternative, and there are other alternatives, too.

I was just too focused on the specific question, comparing IF (SUM) versus SUM(IF()). One is simply incorrect, while the other - well, can be heavy ...

[#7]

Thanks Fry & Oleg,

One of you can explain me how to use Set Analysis in the case I post ?

[#8]

I have seen your answers related to if(sum) and sum(if) ...still, i don't understand the logic after looking in to the application emontant has posted there..would anyone be able to explain it?

if(Sum) is giving out 111 and sum(if) is giving out 1 ....why so????

[#9]

Nobody to explain us how to make this correctly (with Set Analysis or other method) ?

Is the SUM(If) the really good one, when i read this thread, i am not sure : http://community.qlik.com/forums/t/19876.aspx (John perhaps to try to explain...) ?