Announcements
cancel
Showing results for
Did you mean:
Creator III

## if sum syntax

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

1 Solution

Accepted Solutions

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

3 Replies

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

Creator III
Author

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