When writing out formulas in Workday Adaptive Planning, it is common to come across multiple conditions that require complex logic to calculate correctly. Many model builders rely on nested “if” statements to handle these scenarios, often resulting in a very long formula. As the number of conditions increases, these nested “if” statements quickly become difficult to read, maintain, and debug.
The “SWITCH” function is a great solution to offer a cleaner and more structured way to write this same logic. Let’s explore the benefits of using SWITCH formulas and compare them to the traditional nested “if” statements.
Consider this example
We’ll start out simple. Imagine you are creating a formula to calculate the price per doctor’s appointment, depending on different offices. Appointments cost $300 at office 1, $275 at office 2, and $250 at office 3.
In a traditional “if” statement, the formula might look something like this:
iff(this.Office.code = “Office 1”, 300,
iff(this.Office.code = “Office 2”, 275,
iff(this.Office.code = “Office 3”, 250, 0)))
How a SWITCH Formula Works
While this “if” statement isn’t too long, a SWITCH can clean it up a bit. Here’s how it would look:
SWITCH(this.Office.code,
“Office 1”, 300,
“Office 2”, 275,
“Office 3”, 250, 0)
The first value in the formula is what you want to compare against the list of values that follow. In this example, the office code is the value we want the formula to look for. Next, the values of office codes are listed, followed by their respective appointment costs. This formula essentially reads the exact same logic as the “if” statement: if the office is office 1, the appointment cost is 300; if it’s office 2, then 275, and if it’s office 3, then 250, and if it’s none of those, then 0.
Now let’s add some more complexity.
Consider this example
Imagine you are creating a formula to calculate the price per doctor’s appointment, this time dependent on both different offices and different doctor types. Dentist appointments cost $300 at office 1, $275 at office 2, and $250 at office 3. Let’s say orthodontist appointments cost $250 at office 1, $300 at office 2, and $350 at office 3…. and so on for multiple combinations of doctor type and office number.
In a traditional nested “if” statement, the formula might look something like this:
iff(this.Doctor_Type.code = “dentist” and this.Office.code = “Office 1”, 300,
iff(this.Doctor_Type.code = “dentist” and this.Office.code = “Office 2”, 275,
iff(this.Doctor_Type.code = “dentist” and this.Office.code = “Office 3”, 250,
iff(this.Doctor_Type.code = “orthodontist” and this.Office.code = “Office 1”, 250,
iff(this.Doctor_Type.code = “orthodontist” and this.Office.code = “Office 2”, 300,
iff(this.Doctor_Type.code = “orthodontist” and this.Office.code = “Office 3”, 350,
0))))))
While this formula does work, it is:
- Difficult to read
- Repetition can make it hard to follow the logic.
- Hard to debug
- If one condition is incorrect or an additional condition needs to be added, finding the right spot can be frustrating.
- Error prone
- Long nested formulas are more likely to contain syntax or logical errors.
The Solution: a SWITCH Formula
Workday Adaptive Planning’s SWITCH function would work much better to create a formula handling multiple conditions like this. A switch formula relaying the same logic could look something like the following:
SWITCH(this.Doctor_Type.code,
“dentist”, SWITCH(this.Office.code,
“Office 1”, 300,
“Office 2”, 275,
“Office 3”, 250,
0
),
“orthodontist”, SWITCH(this.Office.code,
“Office 1”, 250,
“Office 2”, 300,
“Office 3”, 350,
0
)
)
How it Works
The first value in the formula is what you want to compare against the list of values that follow. In this example, the doctor type is the first value we want the formula to look for.
- For doctor type “dentist,” we have another SWITCH embedded, this time looking at the office code.
- Next, the values of office code are listed, followed by their respective appointment costs.
- This formula essentially reads the exact same logic as the nested if: if the doctor type is dentist AND the office is office 1 (the second SWITCH looks for office code after the doctor type), the appointment cost is 300; if it’s dentist and office 2, then 275, and so on!
- Then, once all of the offices are listed under the “Dentist” SWITCH, the same is written out for orthodontists, with the respective offices and price per appointment.
Why SWITCH Is Better
- Improved Readability
- SWITCH formulas are cleaner and easier to read. The logical flow is clear: first check the Doctor_Type.code, then check the Office.code. Each block is neatly separated, making the formula less overwhelming.
- Easier Maintenance
- Adding or modifying conditions is much simpler with SWITCH. For example, if a new condition for “Office 4” needs to be added, you can insert it directly into the relevant block without reworking the entire formula.
- Reduced Errors
- With SWITCH, there’s less nesting and fewer opportunities for syntax errors (e.g., missing parentheses). Each condition is self-contained, making the formula more robust.
- Logical Separation
- The SWITCH function inherently separates different parts of the logic into manageable sections. In the example above, you can clearly see separate blocks for “dentist,” and “orthodontist,” which improves clarity.
Real-World Benefits for Model Builders
As a Workday Adaptive Planning model builder, using SWITCH formulas can save you significant time and effort, particularly when:
- Handling complex logic with many conditions.
- Working on models that require frequent updates or changes.
- Collaborating with other team members who need to understand your formulas.
Switching to SWITCH will make your models easier to maintain and improve overall performance. While nested “if” statements have their place, they can quickly become unwieldy as your formulas grow in complexity. The next time you’re faced with a complex decision tree in your model, give the SWITCH function a try. You’ll wonder why you didn’t make the switch sooner!
Revelwood is an award-winning, Platinum Solution Provider for Workday Adaptive Planning. We build solutions for the Office of Finance that minimize your risk by seamlessly incorporating business analytics into your everyday thinking. By combining the software with our best practices and out-of-the-box applications, we help businesses achieve their full potential with Workday Adaptive Planning.
Read more Workday Adaptive Planning Tips & Tricks:
Spread Lookups
Forecast Explanations in Predictive Forecaster
2025 R1 Dashboard Improvements – Charts and Perspective Folders
