nested ifs

debbie5154

I have the following nested if, for the highlighted line item (see picture):

IF NOT 'exclude' AND 'Sum fill rate gallons actuals' > 0 THEN 'Sum fill rate gallons actuals' ELSE IF 'Sum fill rate gallons PY' > 0 THEN 'Sum fill rate gallons PY' ELSE IF 'fill rate gallons FOR  movement avg' > 0 THEN 'fill rate gallons FOR  movement avg' ELSE 0

The highlighted row should be returning one of the other line items and it's giving me complete different results.  There is no version on this module or the module the original data comes from.  Any thoughts, or is my formula too complex?  Thank you so much for any suggestions.

obriegr

@debbie5154

Above I have created what your Nested IF tree looks like and interacts.

Assuming you did a direct copy + paste of your line item, and assuming that your time modules are set correctly. I would look into your exlusion property/line item, and or add additional AND statements that interact with the 'exlusion' property. Do you have other line items later in the module that have similar names that is in the wrong place; eg. exclusion vs exclusions.

Your formula is most definitely not too complex, I have some cases that had over 50 if then statements. As a best practice I change the model to complete a lookup of a list and we amend the list to reflect appropriately versus having to rewrite the if-then arguement every few months.

I find it helpful to generate a visual (as I have above; done in Microsoft Visio in under 5 minutes) and generate a literally action by action to make sure that I have the formula working properly. As well as generate what I expect to occur and throw test cases at it (by hand!) and validate that I have the appropriate result.

Sometimes simply re-creating the formula (clearing it out, and rewriting it without copy pasting) can fix some issues.

As the other responder said, there are too many factors for me to give you the answer, but the formula looks close, but I can not say for sure that it is pulling correctly or doing what you want to do.

As a best practice, I would look into your naming convention so that they are consistent (either all fill rate or fill-rate; not intermixed, capitalization is consistent; 'FOR' vs 'for', spacing is consistent; always 1 vs 2 spaces between words, first character of a line item capitalized or not). Some of these things can be ignored, but at that point I would use conditional formatting if you are attempting to bring attention to a particular area of the table of data.

DavidSmith

I have just replied to the original thread, giving advice on how to analyse and split long formulas

 

https://community.anaplan.com/t5/Shared-Best-Practices/Formula-Structure-for-Performance/tac-p/37716#M160

 

David

 

Surya.Arif

Hi Debbie,

Can you also posted the blueprint picture, or the dimension.

Because the information that you gave is to little.

Which calendar type that you use ? Because I see that the column is in week format.

Or you add another list and filled it with weeks ?

and the 'exclude' line item I can not find any reference to that line item.

Lastly if the picture is from your screen shot.

All the numbers are in black which is means that they're all formulas. or it is black because you copy it to excel first.

 

Regards,

 

SA

debbie5154

Thanks to Surya.Arif and obriegr for taking your time to reply.  I'm learning alot about what is needed when asking a question in the forum.  I'll do some more trouble shooting based on your suggestions and post back to this thread.

THANKS!

DavidSmith

Just to add it is best practice and better for performance to split out the component parts of formulaa, especially if the same item/expression is repeated.  It is also better for debugging

If you turn summary options off for these intermediate line items, the size impact will be minimised

You shouldn't need, or build formulas with 50 IFs.  It goes against best practice and will not be the most optimal in terms of performance.  As mentioned above, usually when multiple IFs are needed, it is better to use lookups (and Line Item Subsets sometimes as the mapping module)

See here for more details

https://community.anaplan.com/t5/Shared-Best-Practices/Formula-Structure-for-Performance/ta-p/33177

 

Hope this helps

David

debbie5154

Thanks Mr. Smith.  I remember that from the HUB in Vegas!  But good to remind all of us.