Adjusting Long Formulas Tip

Since trying to follow long conditional formulas can be tricky within the Anaplan formula bar, a tip to make it easier is to use the Drilldown capability on a cell within the line item. By doing this, you're able to see the If Statement/Formula indented with each If Statement. After seeing it in Drilldown, you're able to copy that text into a notepad (like Evernote, not Word) and adjust the formula in the notepad. Once completed with editing, you can copy and paste the formula back into the formula bar. However, the notepad must accurately recognize the single quote symbol and your adjustments must have the exact Anaplan syntax.

Accepted answers

All comments

PrevContributor

Hi Greg,

Thanks for the tip! Quick question, when we've tried this in the past, we've noticed that sometimes important characters are missing from the copy/paste from/to Anaplan. Do you have a specific program or trick you use to make sure nothing is lost along the way of copying out of and back into Anaplan? Thanks!

-Chris Weiss

peter_mcanena

This is really helpful for reading formulae but you should never copy the formula out of the drilldown window for editing. If you do that (as some commenters have noted), you lose some brackets. This is because the drilldown window uses indentation to show the nesting of, e.g. IF statements, rather than brackets, which are less readable but absolutely required for formula editing!

MarkWarren

If a formula is too long to read and format in the Anaplan editor then there is a good chance that formula may be complex in its calculation.
My advice would be to separate parts of the long formula out into new line items, this is definitelty recommended if any parts of the formula are repeated.
The separation should make the formula and module more readable and maintanable and should reduce the calculation cost / improve performance.

As with all performance changes, test before and after (stopwatch time) to know the change has helped...

Example

This formula:

IF ISNOTBLANK(Promotion) THEN IF NOT Promotions.Finished? THEN IF MAX(Product.Costs[LOOKUP: Promotion.Category] - Shipping.Expense[LOOKUP: Shipping Date] + Promo Cost, 0) <> 0 THEN MAX(Shipping.Expense[LOOKUP: Shipping Date] - Product.Costs[LOOKUP: Promotion.Category], 0) ELSE Promo Cost ELSE IF MAX(Product.Costs[LOOKUP: Promotion.Category] - Shipping.Expense[LOOKUP: Shipping Date] + Promo Cost, 0) <> 0 THEN MAX(Shipping.Expense[LOOKUP: Shipping Date] - Storage.Cost[LOOKUP: Export.Location], 0) ELSE Promo Cost ELSE IF MAX(Storage.Cost[LOOKUP: Export.Location] - Shipping.Expense[LOOKUP: Shipping Date] + Promo Cost, 0) <> 0 THEN MAX(Shipping.Expense[LOOKUP: Shipping Date] - Storage.Cost[LOOKUP: Export.Location], 0) ELSE Promo Cost

Can be written as:
IF ISNOTBLANK(Promotion) THEN IF NOT Promotions.Finished? THEN if_a ELSE if_b ELSE IF MAX(lookup_c - lookup_b + Promo Cost, 0) <> 0 THEN MAX(lookup_b - lookup_c, 0) ELSE Promo Cost

where:
if_a = IF MAX(lookup_a - lookup_b + Promo Cost, 0) <> 0 THEN MAX(lookup_b - lookup_a, 0) ELSE Promo Cost
if_b = IF MAX(lookup_a - lookup_b + Promo Cost, 0) <> 0 THEN MAX(lookup_b - lookup_c, 0) ELSE Promo Cost
if_c = IF MAX(lookup_c - lookup_b + Promo Cost, 0) <> 0 THEN MAX(lookup_b - lookup_c, 0) ELSE Promo Cost

lookup_a = Product.Costs[LOOKUP: Promotion.Category]
lookup_b = Shipping.Expense[LOOKUP: Shipping Date]
lookup_c = Storage.Cost[LOOKUP: Export.Location]

Quick Links

Can you help?

Unanswered questions

How have you from-scratch built lot-level, geo-based Inventory Consumption?
We’re working on modeling inventory consumption at the lot level, accounting for shelf life considerations, since we need visibility into which lots may not consume—both for risk reporting and E&O analysis. Each item may have customers with different shelf life requirements. For example, item A may need 270 days for some…
How to bring Row data into Columns?
Hi, Can you help me, how can we get row data into columns using Unique ID? Per attached below first snapshot, I want to bring rountingOrder 4, RecipientName, Sent and Completed dates in 1 row as show in sanpshot 2. Do I need to create Hierarchy list and Map?
How to Design Simulation Planning in Anaplan with Multi-Level User Input and Step-by-Step Logging?
Hi Anaplan Community, I'm working on a simulation planning model where users need the flexibility to input pricing adjustments at any level of the product hierarchy, and I’d love your guidance on best practices. 📌 Scenario Overview: We have a 4-level product hierarchy: Brand Category Sub Category SKU (lowest level) The…