Breaking down the Switch Formula for Workday Adaptive Planning

By: Lauren Taylor

July 29, 2019

Have you ever wondered what that switch formula was under the Logical section of the formula bar?

The switch formula is a handy expression to use instead of many nested if statements.
Adaptive Insight’s formula assistant provides this definition:
Switch (ORIG_EXPR, VAL_OR_RANGE1, EXPR1, VAL_OR_RANGE2, EXPR2, …, DEFAULT_EXPR)
This formula can appear quite confusing, but we will break it down simply for you.
Switch(this.level.currency, “USD”, ASSUM.Tax_USD, “EUR”, ASSUM.Tax_EUR, 0 )
• ORG_EXPR = the original account or expression: In the above example this would be if(this.level.currency)
• VAL_OR_RANGE1 = First Value: In the above example, this would be If(this.level.currency = “USD”)
• EXPR1 = First Expression: In the above example, this would be if(this.level.currency = “USD” then use the Tax USD assumption
• VAL_OR_RANGE2 = Second Value: In the above example, this would be If(this.level.currency = “EUR”)
• EXPR2 = Second Expression: In the above example, this would be if(this.level.currency = “EUR” then use the Tax EUR assumption
• DEFAULT_EXPR = Default Expression, if there is no match then the switch formula will return the default expression: In the above example, this would be 0. For example, if(this.level.currency does not equal USD then 0)
Another example would be if you have an account that needs to calculate differently for varying range assumptions. For instance, in Personnel Planning, an employee may have different vacation expenses based on the length of time the employee has been with the company.
Assumption Account Amount
ACCT.Vacation.0_12_months $5,000
ACCT.Vacation.13_24_months $10,000
ACCT.Vacation.25_36_months $15,000

Rather than writing many nested if statements for example:
IF(ROW.Tenure = Range(0,12),
If the employee’s length at the company is in the Range of 0 – 12 months
Vacation.0_12_months,
then use the Vacation.0_12_months assumption
IF(ROW.Tenure = Range(13,24),
otherwise if the Employee’s length at the company is in the Range of 13 – 24 months
Vacation.13_24_months,
then use the Vacation.13_24_months assumption
IF(ROW.Tenure = Range(25,36),
otherwise if the Employee’s length at the company is in the Range of 25 – 36 months
Vacation.25_36_months,
then use the Vacation.25_36_months assumption
0)))
otherwise use 0.
We can use:
SWITCH(ROW.Tenure, range(0,12), ACCT.Vacation.0_12_months, range(13,24), ACCT.Vacation.13_24_months, range(25,36), ACCT.25_36_months, 0 )
• ROW.Tenure = Account that calculates an employee’s length at the company
• Range(0,12) = 0 – 12 month tenure
• ACCT.Vacation.0_12_months = Assumption account for 0 – 12 months
• Range(13,24) = 13 – 24 month tenure
• Acct.Vacation.13_24_months = Assumption account for 13 – 24 months
• Range(25,36) = 25 – 36 month tenure
• Acct.Vacation.25_36_months = Assumption account for 25 – 36 months
If the Employee has been with the company for 15 months, then the Vacation Account will look up the Acct.Vacation.13_24_months assumption amount. In the above table, the employee’s Vacation expense would be $10,000 for the year.

QueBIT’s Analytics Online Instructor-led Training Courses

  • IBM Planning Analytics Developer
  • IBM Planning Analytics Advanced Reporting
  • IBM Planning Analytics Advanced Developer

Visit learn.quebit.com

QueBIT’s Analytics Online Instructor-led Training

  • IBM Cognos Analytics Authoring Reports Fundamentals
  • IBM Cognos Analytics Authoring Reports with Multidimensional Data
  • IBM Cognos Analytics Authoring Reports Fundamentals

Visit learn.quebit.com

Subscribe to QueBIT Notifications

Can’t find what you’re looking for? →

    Contact Help Desk

    This form collects your contact information so that we can correspond with you. For more information on how we are committed to protecting and respecting your privacy, please review our Privacy Policy.

    I consent to QueBIT collecting my contact information.