topic Re: sum multiple selectable IF statements in QlikView

sum multiple selectable IF statements

Wed, 17 Jun 2015 19:33:26 GMT

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)

)

Re: sum multiple selectable IF statements

ramoncova06 — Wed, 17 Jun 2015 19:38:34 GMT

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

Re: sum multiple selectable IF statements

MarcoWedel — Wed, 17 Jun 2015 19:41:25 GMT

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

Re: sum multiple selectable IF statements

Wed, 17 Jun 2015 21:17:15 GMT

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

Re: sum multiple selectable IF statements

MarcoWedel — Wed, 17 Jun 2015 21:38:09 GMT

you're welcome

regards

Marco

Re: sum multiple selectable IF statements

Thu, 18 Jun 2015 15:52:42 GMT

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

Re: sum multiple selectable IF statements

Thu, 18 Jun 2015 15:56:43 GMT

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