8 Replies Latest reply: Jun 14, 2010 5:42 AM by emontant

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

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 ?

Eric.

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

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.

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

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

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 ?

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

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

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

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.

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

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

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

Thanks Fry & Oleg,

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

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

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

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

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