Another work around method



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. 





  • Hi David,


    Appreciate the help in answering my concern. I will try to do as you mentioned and let you know the result. 



  • 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.

  • 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.

  • 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.

  • 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.