Nested IF statement replacement


I am looking for a best practice for the following.

I have a driver-based model where the user selects a driver from a list of possible options. The user requirement is to use a check box to make the selection. The module displays the options, the check boxes, and the result.

I am looking for a better way to arrive at the result line item. Currently we use a nested if statement on the result line item. i.e. if box 1 then option 1 else if box 2 then option 2 else if box 3 then option 3 and so on.


  • @dan_ossmann

    Take a look at these two articles and see if they help you.

    Bottom line, you will need a calculation module to store the calcs for the drivers, a Line Item Subset (LISS) and then you will be able to do a lookup which will be more scalable and perform much better.

  • I am not really clear how to use the LIS to create the check boxes. I have the calculation module that creates the display of drivers. each driver is a historical reference using lag 3, 6, 9, 12 months ago. each of these is a line item in the calculation module. Those line items are now a LIS.

    The user now wants to check a box each month of the forecast to use one of these drivers in the forecast formula. That selection changes (potentially) each month of the forecast. How do I give the user a check box to select the desired driver?

  • @dan_ossmann

    You actually can't as LISS only work with numbers, but you can create extra line items to be a 1 if the boolean is true, 0 if it is false.

    Can the user select multiple drivers at the same time? If no, then I would use a list formatted line item (similar to the variance analysis article I posted above).

  • So it sounds like my best bet is to tell the user that convenient check boxes are not an option. i.e. change the user requirement and I am golden.