sum multiple selectable IF statements

[#1]

I am using the following to sum multiple fields, where each field can be selected or de-selected through a variable by the user. It seems to work correctly if all variables are set to 0, however as soon as one is de-selected I get null values. Im sure theres something small Im missing here.

I have buttons setup for the three fields that toggle them to either 0 or 1, with the intent these be used by the operator to choose specific values to included.

My expression is below:

If( vFundTypeReal = '0',

Sum(

  {$<[SUMMARY_DATE] = {">=$(vStartDate)<=$(vEndDate)"}>}

HANDLE_REAL)

)

+

If( vFundTypeReleased = '0',

Sum(

  {$<[SUMMARY_DATE] = {">=$(vStartDate)<=$(vEndDate)"}>}

HANDLE_RELEASED_BONUS)

)

+

If( vFundTypePlayable = '0',

Sum(

  {$<[SUMMARY_DATE] = {">=$(vStartDate)<=$(vEndDate)"}>}

HANDLE_PLAYABLE_BONUS)

)

[ramoncova06 · Accepted solution]

I assume that the problem is with + a null value, add a rangesum so that why the null does not create any conflict

sumrange(

If( vFundTypeReal = '0',

Sum(

  {$<[SUMMARY_DATE] = {">=$(vStartDate)<=$(vEndDate)"}>}

HANDLE_REAL)

)

,

If( vFundTypeReleased = '0',

Sum(

  {$<[SUMMARY_DATE] = {">=$(vStartDate)<=$(vEndDate)"}>}

HANDLE_RELEASED_BONUS)

)

,

If( vFundTypePlayable = '0',

Sum(

  {$<[SUMMARY_DATE] = {">=$(vStartDate)<=$(vEndDate)"}>}

HANDLE_PLAYABLE_BONUS)

)

)

or add the a value for each false statement

[MarcoWedel]

‌try RangeSum(if(), if(), if()) instead of if()+if()+if()

[#4]

Fantastic Marco! Thanks for your quick response, works like a charm

[MarcoWedel]

you're welcome

regards

Marco

[#6]

Hi Marco,

I've modified this a few different ways and its been working great, however when I try to modify this to be a count rather than a sum, I don't seem to be getting the results Im expecting. Using the example below, when the PNL_PLAYABLE_BONUS is deselected I just get a result of 0, which is most certainly incorrect. Is there something wrong in the expression or do I need to look elsewhere? I tried a RangeCount but for some reason that didn't seem to work at all.

=If( vFundTypeReal = '1' and vFundTypeReleased = '1' and vFundTypePlayable = '1',
(COUNT(
{$<
[SUMMARY_DATE] = { ">=$(vStartDate)<=$(vEndDate)"}
, [PNL_REAL] = {'>0'}
, [PNL_RELEASED_BONUS] = {'>0'}
, [PNL_PLAYABLE_BONUS] = {'>0'}
>}
DISTINCT [PARTY_ID])
)
,
If( vFundTypeReal = '1' and vFundTypeReleased = '1' and vFundTypePlayable = '0',
(COUNT(
{$<
[SUMMARY_DATE] = { ">=$(vStartDate)<=$(vEndDate)"}
, [PNL_REAL] = {'>0'}
, [PNL_RELEASED_BONUS] = {'>0'}
, [PNL_PLAYABLE_BONUS] = {'=<0.01'}
>}
DISTINCT [PARTY_ID])
)))

[#7]

I did invert the 1 and 0 for the variables to co-incide with standard on/off definition.