line item with no time dimension




I have a module with no time dimension.In this module I have a line item "Contract Amount".

In another module that has time dimension, I also have a line item "Contract Amount". In Jan 2020, value of the contract amount is 100,000. In Feb 2020, value is 120,000.In Mar 2020, value is 130,000.


It is now June 2020. The last value of my contract amount is on Mar 2020 which is 130,000. Now I need that amount 130,000 in my module that has no time dimension. How do I do that?



Best Answer

  • filip.sypniewski

    Hi @Ana22,


    you would need System Module where you identify last month with data (Mar 20).


    Then in your module without time dimension for Contract Amount formula would be OtherModulewithTime[LOOKUP: System Module.LastMonthWithData].


    This should work and makes it dynamic, let me know if you have other questions! 


  • prabhu

    Hi @Ana22,


    Yes it is Possible.


    Step 1: You have a Time Dimension Module and amount line item. 

    Create another Line item(last non blank) of Text format and write the formula as mentioned below.




    Step 2: Change the summary for Line item(last non blank) as mentioned below






    Step 3: Your new module with no time as dimension. write the formula as mentioned below.





    Rename the line item name as per your business case





    Sorna Raja Prabhu

  • Ana22



    Thanks. But is there a way not to use select and specific year? What if it is now 2021. I need to again update the formula in that case.



  • Hi @prabhu,


    nice use of last non blank! Please note that using SELECT is not really recommended by best practices, particularly for specific Time Period e.g. 'FY20', the formula is not really dynamic this way, you need to consider the next year as well, which you can easily achieve using LOOKUP function and system modules. 


    Review this two short lessons:

  • prabhu
    Thank You Filip for suggesting best way.
  • @Ana22 


    System Module with Current Year -> LOOKUP: Current Year

  • Hi @Ana22 


    I agree with @filip.sypniewski , we shouldn't Hard code the time using SELECT and also we should avoid Text line item as much as possible.


    First Approach using TIMESUM:


    1. Create one system module to select start Month(From) and End Month(To).


    2. Already you have Input module just create one line item "last non Zero Amount"




    Formula:  IF Amount = 0 THEN PREVIOUS(Amount) ELSE 0


    3. Create a module without Time dimension:


    Formula:  TIMESUM(Test Last Non Zeros.Last Non Zero Amount, Sys Time Setting.Select Start Month, Sys Time Setting.Select End Month)


    2nd Approach by using LOOKUP:


    1. Create system module for year



    2. Input module will be same  as above.


    3. Create a module without Time dimension:



    Formula: Test Last Non Zeros.Last Non Zero Amount[LOOKUP: Sys Time Setting.Select Year]


    Now your formula in dynamic based on your selection for both approach.


    Hope this helps!