line item with no time dimension

Ana22
Ana22
in Modeling

Hi,

 

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?

 

Thanks!

Tagged:

Best Answer

  • filip.sypniewski
    filip.sypniewski
    Answer ✓

    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! 

Answers

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

     

     Capture.JPG

     

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

     

     

    Capture1.JPG

     

     

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

     

    Capture2.JPG

     

     

    Rename the line item name as per your business case

     

     

     

    Thanks,

    Sorna Raja Prabhu

  • Ana22
    Ana22

    Hi,

     

    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.

     

    Regards,
    Ana

  • filip.sypniewski
    filip.sypniewski

    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:

    https://community.anaplan.com/t5/On-Demand-Courses/Avoiding-Hard-Coding/ta-p/64238

    https://community.anaplan.com/t5/On-Demand-Courses/Using-SELECT/ta-p/63590

  • prabhu
    prabhu
    Thank You Filip for suggesting best way.
  • filip.sypniewski
    filip.sypniewski

    @Ana22 

     

    System Module with Current Year -> LOOKUP: Current Year

  • Akhtar.shahbaz
    Akhtar.shahbaz

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

    Screenshot_167.png

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

     

    Screenshot_172.png

     

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

     

    3. Create a module without Time dimension:

    Screenshot_169.png

    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

    Screenshot_170.png

     

    2. Input module will be same  as above.

     

    3. Create a module without Time dimension:

    Screenshot_171.png

     

    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!

    Thanks

    Akhtar

Categories