SUM (IF...) vs IF(SUM(...))

[markmccoid]

Hi,

When I create expressions with an IF statement I remember being told to always put the SUM on the outside of the IF.

I was wondering if anyone could expand on how Qlikview handles expressions when the SUM is on the outside of the IF versus when the IF is on the outside.

Example:

IF(Day='SUN', 0, SUM(Revenue))

VS.

SUM(IF(Day='SUN', 0, Revenue))

Thanks,

Mark

[hector]

Hi, if Day is a dimension in your table, or you select 'SUN' in the listbox, the 1st expression will work, the second one will work always.

c ya

[markmccoid]

Could anyone elaborate on this? I'm trying to get a view of how the expression engine is going through records evaluating the expression and coming up with the results.

Thanks,

Mark

[Anonymous · Accepted solution]

Expression IF(Day='SUN', 0, SUM(Revenue))
First it checks if SUN is selected in the Day field. If yes, the expression returns 0. If not, the expression returns sum of Revenue for all days in selection. For example, if all days are selected (available), the result will include all Revenue, including Sunday.

Expression SUM(IF(Day='SUN', 0, Revenue))
It returns sum of Revenue within selections where day is not Sunday.

The result of both expressions will be identical only if you select all days except SUN.

[markmccoid]

Hi,

I'm writing some training for Power Users and trying to explain why they should use SUM(IF,,,,) vs. IF(...., SUM())

Since I'm still a bit unsure of myself on this topic, could someone do a quick proof read of thefollowing training excerpt?

-------------------------------------------------

You can use IF…THEN logic in your functions too. An example would be if you wanted a column to only show revenue for Color Charges. You could write this expression as follows:

sum(IF(ChargeSubCategory='Color Rate Insert',[~AMOUNT_Revenue],0))

Note that the SUM function is on the outside of the IF statement. This is very important, if you put the sum around the [~AMOUNT_Revenue] you will get inaccurate results.

You can think of the IF statement as testing every transaction and checking to see if its ChargeSubCategory is equal to 'Color Rate Insert', if it is, it will return the Revenue amount to be part of the SUM function otherwise it will return a zero. So when all the transactions have been processed the SUM function can add up all the transactions that passed the test and return your value.

Why you probably don't want the SUM statement on the inside of an IF statement. Here is an example:

 IF(ChargeSubCategory='Color RateInsert',SUM([~AMOUNT_Revenue]),0)

In this expression QlikView will first see if ChargeSubCategory is equal to 'Color Rate Insert', but it doesn't check each transaction, but instead it checks to see if this is the only possible value for ChargeSubCategory, if it is, then it returns SUM([~AMOUNT_Revenue]) else it will return zero.

-----------------------------------------

Thanks!

[johnw]

That looks correct to me on brief glance. However, I think I use if(...,sum()) as often as I use sum(if(...)). Which you use depends entirely on the situation. I wouldn't want to steer the power users away from one or the other. I would simply explain how each is used.

Put anther way, it's kind of like asking if you should do this:

FOR I = 1 TO 10
IF CONDITION
DO SOMETHING
END-IF
END-FOR

Or this:

IF CONDITION
FOR I = 1 TO 10
DO SOMETHING
END-IF
END-FOR

There is no right answer except for specific cases. Both are valid solutions to different problems. I would never tell a programmer "Always put your IF inside of the FOR".

[Anonymous]

I certainly use sum(if(...)) more often than if(..., sum()) - but agree 100% that none of them is "wrong". It depends on what is actually required in any specific situation.

[markmccoid]

Thanks for the input. I'm not sure why this kind of logic was difficult for me but I think I'm understanding it a bit more.

I always think of the expression in a chart as looping through all the transactions and performing the IF on each transaction. But with the IF on the outside, it doesn't look to see if a particular transaction has a field that meets the condition I.E. Day = Sunday, it looks to see if that field has Sunday selected or is the only possible value.

Sorry to belabor the issue, but I just to make sure I understand how QV is handling this stuff.

I appreciate all your input, it is helping a lot.

[johnw]

I think that's a good way to think of it. Sum(if()) loops through all the records, performing the if() on each. If(sum()) does the if first, and if it is satisfied, then loops through all the records.