topic Re: if sum syntax in QlikView

if sum syntax

gerrycastellino — Wed, 09 Sep 2015 21:46:13 GMT

What is the proper way to structure this is statement, I'm getting the error below when I do not have a period dimension selected:

=if(isnull(only(PeriodBlockChoice)),sum([Day # Envelopes Sent] * _FiscCurrYTDFlag) ,sum([Day # Envelopes Sent] * $(=only(PeriodBlockChoice))) )

Re: if sum syntax

Oleg_Troyansky — Wed, 09 Sep 2015 22:09:23 GMT

Try enclosing the ONLY() function in the RangeSum() function - this way, the possible NULL value will get replaced with a zero, and the expression will be valid:

=if(isnull(only(PeriodBlockChoice)),sum([Day # Envelopes Sent] * _FiscCurrYTDFlag) ,sum([Day # Envelopes Sent] * $(=rangesum(only(PeriodBlockChoice)))) )

cheers,

Oleg Troyansky

QlikView Your Business: An expert guide to Business Discovery with QlikView and Qlik Sense

Re: if sum syntax

gerrycastellino — Wed, 09 Sep 2015 22:37:27 GMT

not quite, the rangesum returns '0,1'. I need it to return the value of the my period flag, ie:

_MTDFlag

Re: if sum syntax

Oleg_Troyansky — Fri, 11 Sep 2015 14:32:41 GMT

OK, I understand... So, here is the problem: when you don't have the Period Block Choice selected, the function only returns NULL(), and the whole expression looks like this:

=if(isnull(only(PeriodBlockChoice)),sum([Day # Envelopes Sent] * _FiscCurrYTDFlag) ,sum([Day # Envelopes Sent] * <null>)) )

which is of course incorrect. What if you replace the second ONLY() with a MaxString() function? This way, the formula will remain syntactically correct, and the logic will be preserved because of the IF condition:

=if(isnull(only(PeriodBlockChoice)),sum([Day # Envelopes Sent] * _FiscCurrYTDFlag) ,sum([Day # Envelopes Sent] * $(=MaxString(PeriodBlockChoice))) )

cheers,

OlegTroyansky

Check out my new book QlikView Your Business!