Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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))) )
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!
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
not quite, the rangesum returns '0,1'. I need it to return the value of the my period flag, ie:
_MTDFlag
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!