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
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
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
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
then use the Vacation.25_36_months assumption
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.