topic Re: SUMIF versus IF in QlikView

SUMIF versus IF

Anonymous — Mon, 30 May 2016 15:39:37 GMT

Hello,

I am trying to write a simple expression in QlikView. While I do get the right answer, I do not understand why only one of two approaches is working.

More specifically, when I try to write the following expression, nothing executes and the chart collapses unless I have the filter selected (i.e., if filter for 'Par' in the Risk list box, then the expressions executes correctly):

=if([Risk] ='Par',
sum(Cashflow)
,0)

However, when I use the following Sumif equation, the expressions executes correctly:

=sum(if([Risk] = 'Par',
Cashflow
,0))

Does anyone happen to know why this is the case; in my opinion, both expressions should provide the same output.

Re: SUMIF versus IF

Kushal_Chawda — Mon, 30 May 2016 16:28:33 GMT

use set analysis instead Sum if

alt(sum({<[Risk] = {'Par'}>}Cashflow) ,0)

Re: SUMIF versus IF

sunny_talwar — Mon, 30 May 2016 16:29:40 GMT

They are very different. In first you are saying that if the dimension Risk = Par then show me the Sum(Cashflow). So this in a chart with Risk as dimension would show you Sum(Cashflow) only where Risk = Par. Whereas the other one is saying that regardless of your dimension sum only those rows where Risk = Par.

Does that make sense?

Re: SUMIF versus IF

miguelbraga — Mon, 30 May 2016 16:35:07 GMT

Hi Michael,

These two expression although seems doing the same thing, they have a particular difference.

That difference is that in the first case you need to apply a filter like choosing one option on your listbox of the field [Risk] and in the other you don't need any selection. Another way of doing this should be using set analysis in your sum like this:

=sum({1<Risk = 'Par'>} Cashflow) -> if doesn't matter your selections

=sum({$<Risk = 'Par'>} Cashflow) -> if matters your selections

Hope this can hel you

Regards,

Some nice tip: don't use sums with if's, try to implement your if's toicreate flags then use those flags inside the if's like this example: [ =sum(flag * Cashflow) ] -> This case flag in the script should be assigned like this: if(Risk = 'Par', 1, 0) as flag

Re: SUMIF versus IF

Kushal_Chawda — Mon, 30 May 2016 16:37:49 GMT

see this

Conditional Aggregations