Skip to main content

Write good formulas in Adaptive

Mastering the Art of Formula Writing in Workday Adaptive Planning

Have you ever encountered a formula in Workday Adaptive Planning that seemed like a cryptic puzzle that made your eyes sting or cry? We've all been there. While formulas are the backbone of powerful models, poorly written ones can hinder understanding, efficiency, and maintainability.

To ensure your formulas are clear, concise, and easy to comprehend, let's explore these four essential principles:

1. Use and abuse the SPACE BAR (talking about keyboard here!)

Just as a well-formatted document is easier to read, a formula with ample white spacing is easier to decipher too! This simple technique will significantly improve the redability.

  • Visual Cues: Use spaces between operators, functions, and variables to visually separate elements. DontwriteyourformulaslikethisbasicallyOK?
  • Here is an example of a formula that calculates the benefits for a fulltime employee whether they are USA based or International: 
🥺 SKIBIDI: 

iff(ROW.EmployeeType="FTE",iff(ROW.Country="USA",ASSUM.BenefitsAmount_USA,ASSUM.BenefitsAmount_International)*ROW.Headcount,0)

A little better:

iff (ROW.EmployeeType = "FTE", iff (ROW.Country = "USA", ASSUM.BenefitsAmount_USA, ASSUM.BenefitsAmount_International) * ROW.Headcount, 0)

2. Leverage Line Breaks

  • Modularize Your Formulas: Break down complex formulas into smaller, more manageable chunks by using line breaks to separate logical sections. This technique helps you and others grasp the formula's intent more quickly.
Much better:

iff (ROW.EmployeeType = "FTE", 
iff (ROW.Country="USA", 
ASSUM.BenefitsAmount_USA, 
ASSUM.BenefitsAmount_International) * ROW.Headcount, 0)

 

3. Employ Indentation

  • Hierarchical Organization: Indent nested expressions to visually represent their hierarchy.
  • This technique highlights the relationship between different parts of the formula and makes debugging much faster! For example we can see more easily what IF statement a closing parenthesis is related to!
Fantastamazing:

iff (ROW.EmployeeType = "FTE", 
        iff (ROW.Country="USA", 
                ASSUM.BenefitsAmount_USA,  
                ASSUM.BenefitsAmount_International 
        ) * ROW.Headcount,  
        0  
)

 

4. Harness the Power of Comments

  • Explain Your Intent: Use comments to clarify the purpose of specific parts of your formula.
  • Future-Proof Your Work: Comments help others (and your future self) understand the rationale behind your choices.
  • Best Practices: Add comments to complex calculations, non-obvious logic, and any assumptions made.
  • In Workday Adaptive, start your comments with the # symbol. Anything after a # symbol until you press Enter is considered a comment and is not interpreted by the formula.

🤩 GOAT! 

# Benefits for Fulltime employees only, else 0 
iff (ROW.EmployeeType = "FTE", 
        iff (ROW.Country="USA", 
                ASSUM.BenefitsAmount_USA,               # USA Benefits Amount
                ASSUM.BenefitsAmount_International   # Other Countries
        ) * ROW.Headcount,  
        0                                                                         # else 0 for non fulltimers
)


Beyond the Basics

For more in-depth guidance on formula writing, consult the Workday Adaptive Planning Help Guide.

Need Expert Assistance?

Please reach out!


By adhering to these principles, you can create formulas that are not only functional but also elegant and fluid. Remember, well-written formulas are a testament to your modeling prowess and a valuable asset to your organization.

Check my video if you don't like reading!



Labels:
Location: San Francisco, CA, USA

Comments

Post a Comment

Popular posts from this blog

Adaptive's API

To integrate data in Workday Adaptive Planning, customers typically use the integration module to connect with ERPs, SFTPs, AWS S3, Snowflake, SalesForce, Excel, and Google Sheets, etc.  You can also use third party platforms such as Tray.ai , Boomi , Matilion etc. which leverage Adaptive's APIs in a user friendly setup.  And lastly, you can write your very own scripts (C#, Python, Powershell...) to make these API calls.  Most  common Adaptive's API use cases Import Data from ERPs Actual summaries and transaction details (by supplier, employee...) Assets, amortization, depreciation Payroll details and taxes Import Sales data from CRMs or datalakes Pipeline, churn, opportunities and customers  Billings, bookings, revenue, ARR Import Headcount data from an HRIS Current headcount and terminations Hired, not started Wages and compensation plans Benefits and other demographics Import Other data: Exchange Rates Weather Meta data: currencies, attributes, dimensions suc...
Post a Comment
Read more

What are Metric accounts and when to use them?

 I am often asked: "What are Metric accounts in Adaptive and what is their use case?" Before we jump into the weeds, you can create "global" metric accounts, called standard metric accounts  and within cube sheets.  To create a standard metric account, go to:  Menu > Modeling > Metric Accounts Adaptive will come with a couple of pre-built Metric accounts, such as Gross Margin % What are Metric Accounts? A metric account in Adaptive is a calculated account , meaning it carries a formula .  It does   not carry any data entry .  It can not be a rollup account,  meaning it can not have any "children accounts" rolling up to a parent. Metric accounts are typically used to calculate ratios , this is why they can only be either a number or a percent ! But... there is more subtility in them! Metric accounts are different from the other calculated accounts (custom, modeled, cube etc.) in the sense that their formula is also computed at the level an...
Post a Comment
Read more

One (perfect) Headcount Planning Process in Workday Adaptive for FP&A

FP&A: The Perfect Headcount Planning Process in Workday Adaptive for non Platform customers: Streamlining integration, reconciliation and budgeting This article is for companies that are not on the Workday Platform  (HCM, FINS, Adaptive) and are missing out on the integrated cycle of hiring, planning, reconciliation, and system-wide alignment that Workday offers. I sometimes hear complaints (luckily not too much) about Workday HCM, Adaptive or FINS... but the truth is, no other tool on the market currently offers a complete solution that spans the entire hiring process—from budgeting and requesting to hiring, reconciling, and publishing plans across systems. Workday is the only one with this capability right now!  I’m not paid by Workday to write this… But after working with several HRIS, ATS, and ERP systems, I can confidently say that Workday stands out for its customizable workflows, overall ease of use, and seamless communication across all three systems -- assuming yo...
Post a Comment
Read more