Highlighted
Contributor

line item with no time dimension

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!

7 REPLIES 7
Highlighted
Frequent Contributor

Re: line item with no time dimension

Hi @Ana_M,

 

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! 

Highlighted
Super Contributor

Re: line item with no time dimension

Hi @Ana_M,

 

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

Highlighted
Contributor

Re: line item with no time dimension

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

Highlighted
Frequent Contributor

Re: line item with no time dimension

@Ana_M 

 

System Module with Current Year -> LOOKUP: Current Year

Highlighted
Regular Contributor

Re: line item with no time dimension

Hi @Ana_M 

 

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

Highlighted
Frequent Contributor

Re: line item with no time dimension

Hi @Sorna Raja 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

Highlighted
Super Contributor

Re: line item with no time dimension

Thank You Filip for suggesting best way.