if sum syntax

[gerrycastellino]

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

[Oleg_Troyansky]

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

[gerrycastellino]

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

_MTDFlag 

[Oleg_Troyansky · Accepted solution]

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!