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/2019	2/1/2019
201	3/1/2019	4/1/2020
301	5/1/2020	blank

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-2019	Feb-19	Mar-19	Apr-19	May-19	Jun-19	Jul-19	Aug-19	Sep-19	Oct-19	Nov-19	Dec-19	Jan-20	Feb-20	Mar-20	Apr-20	May-20	Jun-20	Jul-20	Aug-20	Sep-20	Oct-20	Nov-20	Dec-20
value_over_months	101	101	201	201	201	201	201	201	201	201	201	201	201	201	201	201	301	301	301	301	301	301	301	301

How can I achieve this?

Accepted answers

rob_marshall

@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]

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

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

Hope this works for you,

Rob

All comments

JLD

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

mohitveer_sandhu

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.

rob_marshall

@mohitveer_sandhu

See if this helps.

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

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

Hope this works for you,

Rob

mohitveer_sandhu

@rob_marshall Thanks, will try this.

mohitveer_sandhu

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:

value	start date	end date
101	1/1/2019	1/4/2019
102	1/5/2019	1/20/2019
103	1/21/2019	2/28/2019

Module B should have

	Jan-20	Feb-20	----
value	102	103	----

rob_marshall

@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]

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

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

Hope this works for you,

Rob

mohitveer_sandhu

@rob_marshall Thank You!!

Quick Links

Can you help?

Unanswered questions

Create a Filter to show all the elements of that level based on selection in hierarchy
Hello Community members, Requirement Details: I have 5 levels of composite hierarchy list (A1, A2, A3, A4, A5). I have published a module in UX, Now the requirement is If user select a list member from A4 level in the context selector or Custom Filter I have to display all the list members under that A4 level. How can I…
import monthly data with months in columns
is there a way to import data with months in columns (usual excel table view)? I have to transform the data so there are rows to cover each period in the upload data form, but I am wondering if there is a better way to just import the data as is with the months in the columns. please let me know what you think. thank you,
Cloudworks Big Query Integration Fail
Hello, I am receiving an 'Anaplan Upload Failed' Status Description when testing my integration with a Big Query dataset. The integration imports data from BQ to our Anaplan model. No other details given in the error log. I suspect that Cloudworks is not even picking up the file but am not sure what we did wrong on the set…