Nested If statement help

[Anonymous]

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

[sunny_talwar · Accepted solution]

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

[Anonymous]

Perfect, worked as expected.

Thank you,

Justin

[Anonymous]

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

[sunny_talwar]

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

Best,

Sunny

[sunny_talwar]

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

[Anonymous]

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

[sunny_talwar]

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