Another work around method

Jsdeloria21

Hi,

 

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. 

 

Regards,

Satisfaction_Rate_42148.docx

DavidSmith

@Jsdeloria21 

Yes!!

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

Happy modelling

David