Drill Down

ShivankurSharma
ShivankurSharma
in Modeling

Hi, I have a formula applied to a line item which is not giving out the correct result, and if I drill down the formula, the pop-up screen is not considering he other modules for calculation which are there in the formula. Can you help where is the lag in the formula. Have attached a screenshot for reference.

 

Thanks.

Tagged:

Best Answer

  • Misbah
    Misbah
    Answer ✓

    @ShivankurSharma 

     

    Glad that you figured out the issue. However I would like to comment on your formula set up.

     

    1. You DO NOT need to have a selection module to write IF ELSE Statements. You can get away selection module unless you are planning to filter it on that. You can club your IF ELSE condition for Transaction count & Deal Count since the output is to be same for these two conditions. (This Approach is not considered to be best practice if you have more than two if else statements)

    2. Since you have multiple IF ELSE Statements in your formula it is not considered to be a best practice instead you can break this up by having mapping modules and then sum over the mapping. For Example in your formula there are multiple IF ELSE statements with a total character count of more than 750. System has to look for each condition, satisfy it and then move on to the next condition. - this might be degrading for model performance esp when there are so many IF Else statements.  I would recommend

       a) Create a mapping module with GL Sub Sector Code as a dimension and create three line items - All three line items should be                 formatted as New Recovery List      

    Misbah_0-1586335923499.png

       b) Create another 3 line items in your staging module - which is Invoice details module and get the mapping from GL Sub Sector                  module to this module

        

    Misbah_1-1586336108324.png

     

    c) Go to your Target Module and Aggregate the numbers based on all these line items

    Misbah_2-1586336275829.png

    Character Count Before >750

    Character Count After <550

     

    Hope that helps

    Misbah

     

Answers

Categories