topic Re: Nested If statement help in QlikView

Nested If statement help

Anonymous — Mon, 04 May 2015 18:48:12 GMT

Hello,

I am not sure how to properly set up my If statement to achieve the results desired. Here is my code and what I am looking to do:

=IF(SubStringCount(Concat([_dimension], '|'), 'Month'and 'New / Renew'),

Sum(EIF),

IF(SubStringCount(Concat([_dimension], '|'), 'Month'),

sum({<[NEW RENEW IND]={$(vCombinedChoice)}>}[EIF]),

sum({<[MONTH-NUMERIC] ={$(vMaxMonth)}, [NEW RENEW IND]={$(vCombinedChoice)}>}[EIF])))

I have created a report builder and I am looking to change the results based on the selection. If user selects both Month and New / Renew dimensions and anything else then I just want a basic sum of EIF.

If the user selects Month and anything else that is not New / Renew, then I want it to sum with new renew = combined or user selection.

Finally, if neither Month or New / Renew are selected then I want new renew = combined / user selection and month = highest month selected.

My expressions are correct, I just need help on the if statements picking up the proper dimensions.

Thank you for the help,

Justin

Re: Nested If statement help

sunny_talwar — Mon, 04 May 2015 18:52:05 GMT

Try this may be:

=If(SubStringCount(Concat([_dimension], '|'), 'Month') = 1 and SubStringCount(Concat([_dimension], '|'), 'New / Renew') = 1, Sum(EIF),

If(SubStringCount(Concat([_dimension], '|'), 'Month') = 1 and SubStringCount(Concat([_dimension], '|'), 'New / Renew') <> 1,

Sum({<[NEW RENEW IND]={$(vCombinedChoice)}>}[EIF]),

If(SubStringCount(Concat([_dimension], '|'), 'Month') <> 1 and SubStringCount(Concat([_dimension], '|'), 'New / Renew') <> 1,

Sum({<[MONTH-NUMERIC] ={$(vMaxMonth)}, [NEW RENEW IND]={$(vCombinedChoice)}>}[EIF]))))

Re: Nested If statement help

Anonymous — Mon, 04 May 2015 19:17:31 GMT

Perfect, worked as expected.

Thank you,

Justin

Re: Nested If statement help

Anonymous — Mon, 04 May 2015 19:26:09 GMT

It seemed to be correct, but when I input only New / Renew, it now comes up as a null value. Any thoughts?

Re: Nested If statement help

sunny_talwar — Mon, 04 May 2015 19:34:40 GMT

Awesome! I wasn't sure if I placed all the parenthesis correctly or not. But I am glad it works

Best,

Sunny

Re: Nested If statement help

sunny_talwar — Mon, 04 May 2015 19:36:54 GMT

New / Renew needs to exactly match what we have in the if statement. Spaces and cases needs to exactly match. Are they matching? If not fix the expression accordingly.

Or if you can share the application.

Best,

Sunny

Re: Nested If statement help

Anonymous — Mon, 04 May 2015 19:39:36 GMT

Sunny,

It seems as there needed to be a statement where New / Renew =1 and Month <> 1. I added that to have 4 total statements and it works fine.

Thank you again for the help,

Justin

Re: Nested If statement help

sunny_talwar — Mon, 04 May 2015 19:44:40 GMT

Oh okay, I just saw three statements to begin with and was not sure if there were 3 or 4 different results. But I am glad it all worked out.

Best,

Sunny