Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)
)
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
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
try RangeSum(if(), if(), if()) instead of if()+if()+if()
Fantastic Marco! Thanks for your quick response, works like a charm
you're welcome
regards
Marco
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])
)))
I did invert the 1 and 0 for the variables to co-incide with standard on/off definition.