Another work around method
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,
Comments
-
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
6 -
Hi David,
Appreciate the help in answering my concern. I will try to do as you mentioned and let you know the result.
Regards,
0 -
Reviving an old thread as I have been working through a similar case and wondering if there's a better way. I should have known that David already solved it! 🙂
I struggle a bit with the solution though in terms of Auditability (A in PLANS.) While the IFs + SELECTs definitely feel dirty with the hardcoding, it is done within 1 line item and very easy for someone else coming in to understand what is going on.
The "sexier" solution above adds an extra list and 2 extra modules with ~10 additional line items to do the same thing. While the calculation may be more efficient, it definitely doesn't seem simpler - there are more items of maintenance/organization and it would be harder & more time-consuming for someone else coming in to understand what's going on. For cases with a small-ish, unchanging number of tiers, the trade-off hardly seems worth it.
I'm curious how others approach this.
As a minor point of efficiency with the IFs + SELECTs formula: if the tiers are continuous, it's unnecessary to evaluate both min and max every time. It's enough to move upwards and check against max only (+ a single min check for the lowest tier if needed), or downwards and check min only (+ a single max for the highest tier if needed,) picking direction based on what's most common. This reduces the calculations as well as the length and complexity of the formula.
1 -
Thanks @helennie for this. I'm always amazed at @DavidSmith solutions - both in terms of detail and in how he relates everything back to best practices.
I think you bring up a good point about PLANS - one that I've struggled with a lot too. Sometimes it seems in an attempt to make things super efficient we make it harder to follow or trace back. I guess I'm still new enough to model building that I fallback on best practices every time. But I know the day will come where I'll see things on a spectrum.
Anyway, I really like your idea. Also, I like the conversation you started about exiting IF statements early or use AND/OR. Not sure we completely resolved that one. I'm still very curious on performance which is better.
1 -
Thanks @JaredDolich!
I love the best practices too, but having recently inherited an enormously complex model with massive numbers of lists and modules to sort through, I've really developed a new appreciation for simplicity! It's a tricky balance sometimes between space, performance, and auditability - it's awesome that they align most of the time, but can make for a tough decision when it seems like they don't!
As for the IFs vs AND/OR discussion, my interpretation of Ben's statement about AND/OR (right hand side is only evaluated if needed) is that it would perform similarly to the nested IFs assuming everything is ordered optimally, so it doesn't come with the performance inefficiency as I thought. Thus, I've switched to using AND/OR where appropriate instead of the nested IFs.
1 -
Hi David,
In same case , if my decision grid define at multiple list level and by month on month and these lists masters mapping are maintained in transactions modules ,then how we will capture the decision in transaction module.
In above case i am assuming decision grid applicability at list level can be change by month on month.
0
