Add value to a given Time Duration

Highlighted
Occasional Contributor

Add value to a given Time Duration

Hi,

 

I have a Module A with the following line items, format, and data:

value(Format = Number)start(Format = Date (mm/dd/yyyy))end(Format = Date (mm/dd/yyyy))

101

1/1/20192/1/2019
2013/1/20194/1/2020
3015/1/2020blank

 

I have another module B with Time Dimension and a Line item - value_over_months that will be mapped to module A and expected data to be as given below:

 Jan-2019Feb-19Mar-19Apr-19May-19Jun-19Jul-19Aug-19Sep-19Oct-19Nov-19Dec-19Jan-20Feb-20Mar-20Apr-20May-20Jun-20Jul-20Aug-20Sep-20Oct-20Nov-20Dec-20
value_over_months101101201201201201201201201 201201201201201201201301301301301301301301301

 

How can I achieve this?

7 REPLIES 7
Highlighted
Certified Master Anaplanner
Certified Master Anaplanner

Re: Add value to a given Time Duration

Hi @mohitveer_sandhu,

You can add the time (month) in the same dimension as module A. On the formula, you can try something like if the month (time) is between start and end get the item(value) else blank. Handle the summary, you can use (for eg.) max. Then reference it in your module B.

I hope this helps you out. 

 

Jud

Highlighted
Occasional Contributor

Re: Add value to a given Time Duration

Can you elaborate on how can I add time(month) in module A?

And also I have a restriction that module A can have only this schema.

Highlighted
Master Anaplanner/Community Boss

Re: Add value to a given Time Duration

@mohitveer_sandhu 

 

See if this helps.

 

I have replicated your data, but changed the years to fit my model:

 

2020-10-27_08-04-27.png

 

In the target module, I have these two line items:

Value: Data of Numbered LIst.Value[SUM: Data of Numbered LIst.Start Date] where Data Of Numbered List is the name of the source module (the one above)

Value Over Months:IF Value = 0 THEN PREVIOUS(value over months) ELSE Value

 

2020-10-27_08-06-35.png

 

2020-10-27_08-06-49.png

 

2020-10-27_08-07-03.png

 

Hope this works for you,

 

Rob

Highlighted
Occasional Contributor

Re: Add value to a given Time Duration

@rob_marshall Thanks, will try this.

Highlighted
Occasional Contributor

Re: Add value to a given Time Duration

This works but if we want to extend this to such a data where for a single month there can be multiple values and we want to take only the value that’s true for the majority of the month.

 

For example:

valuestart dateend date
1011/1/20191/4/2019
1021/5/20191/20/2019
1031/21/20192/28/2019

 

Module B should have

 Jan-20Feb-20----
value102103----
Highlighted
Master Anaplanner/Community Boss

Re: Add value to a given Time Duration

@mohitveer_sandhu 

 

So the requirements have changed since the original post?   The best way to do this is to fix the data at the source so you are only loading the correct values that you need.

 

With that said, I believe this will work:

 

In the first module, create these new line items.

Real Starting Month: period(start(date))

New Starting Month: IF PERIOD(Start Date) <> PERIOD(End Date) THEN Real Starting Month + 1 ELSE Real Starting Month

Days: IF End Date <= 'SYS Time Filter - Month'.End[LOOKUP: Real Starting Month] THEN End Date - Start Date ELSE 'SYS Time Filter - Month'.End[LOOKUP: New Starting Month] - Start Date

Month to Use: leave blank for now

 

Create a new module dimensionalized by Time:

Max: Data of Numbered LIst.Days[MAX: Data of Numbered LIst.New Starting Month]

2020-10-27_10-48-28.png2020-10-27_10-48-38.png

 

Go back to the "data" module and fill in the Month to Use formula:

IF Days = Max Nbr of Days.Max[LOOKUP: New Starting Month] THEN New Starting Month ELSE BLANK

 

2020-10-27_10-50-54.png

 

2020-10-27_10-51-15.png

 

In the target module, change the original sum to now use Month to Use:

 

2020-10-27_10-53-34.png

 

Hope this works for you,

 

Rob

Highlighted
Occasional Contributor

Re: Add value to a given Time Duration

@rob_marshall Thank You!!