I would like to ask if there are any other work around for the attached issue. Please see attached document for details. I was able to get the desired result using the IF function but I want to know whether there is another approach to it.
Solved! Go to Solution.
As you've already guessed (and thank you for asking!) multiple level IFs and SELECT statements are not best practice; they hinder performance and the hard coded nature of the SELECT is bad for flexibility and sustainability.
So, the solution is to model the thresholds separately. There are a number of steps, but the "tiering" concept is commonly used, so it is worth pursuing. The staging modules are not large, so should not have a material effect on the model size
I have replicated your Rates and Score module as follows:1. Create a flat list of numbers covering the range of possible numbers (0-132). I've gone one more than the maximum to get the Maximum threshold to calculate correctly
2. Create a module to hold the values of the rates; it is best practice to have these in a model rather than list properties
The formula for Code is CODE(ITEM(Rates List))
3. Create a calculation module dimensioned by the Satisfaction Rates List (from the first module), and the Rates List you created in 1.
4. We will now check each "rate" against the thresholds to see if they fall within the Satisfaction Rate. I have shown all three steps in the calculation; It is possible to combine them, but is best practice to split calculations wherever possible for performance and auditing. After we have found if the Rate falls within the thresholds, we need to associate the Satisfaction Rate with it and we use a Summary method to bring the Satisfaction Rate to the Top Level. This allows us to link to it without using a SELECT statement
The blueprint is as follows:
5. We now need to link this back to the target module. We have to convert the numeric % value into the Rates List, hence why I have used the ROUND function. Once we know which Rates List Item the numeric value represents, we can LOOKUP the Satisfaction Rate and then in turn look up the text value
The blueprint is as follows:
The calculations are only done using the appropriate dimensionality and the formulas and structures are broken up to increase efficiency and performance. This method is also very flexible as there are no SELECT statements.
Anaplan is a very flexible modelling patform and there are often multiple ways to achive the desired solution. However if, as you thought, "this doesn't felel right" or "there must be a better way", there probably is and that way will almost always be more efficient and simpler.
Thank you for posting the question and I hope this helps and provides a technique you can use for other similar "tiering" calculations
Appreciate the help in answering my concern. I will try to do as you mentioned and let you know the result.