Adjusting Long Formulas Tip

greg_kajewski
greg_kajewski
in Modeling

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. 

Tagged:

Answers

  • PrevContributor
    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
    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
    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]

     

     

     

Categories