Add columns in load script to evenly distribute costs through time

[Enriquemmesteo]

Hello everyone,

we have the following request and need help to implement it:

To make simple, lets say we have these fields in our dataset:"Activity", "Cost","End Date","Today Date".

Currently we load these and have a representation of the evolution of a project's cost in a line chart.

This line chart has a "Common Date" in the X axis, and Y axis represents the % of the total cost based on the cost of the activities in each "End Date".(it's an aggregated value).

This means that we represent each "Cost" as incurred on his "End Date", which we then load as "Common Date" too so we can relate it to the time dimension common to all documents loaded in the application.

This is not completely accurate because the cost is actually distributed evenly through the duration of the activity, and not all at once.

What we want to do from the load script is...having the "Cost" and the number of months remaining from "Today Date" to "End Date", load the corresponding fraction of the cost to each month until "End Date"

We then need to have all these fractions related to "Cost" and periods related to "Common Date" to represent it in the curve.

Here is an example

We have different activities with different End Dates. We currently represent 100 at 01/12/2020 for A and 200 at 06/05/2020 for B.

We need to distribute those 100 into the 5 months between "Today Date" and "End Date". 20 each of the 5 months for A and 20 too for each of the 10 months. This way the curve is more realistic and we avoid those "steps" in costs at certain dates.

Hope somebody is able to help with this.  I guess we should be looping for each number of months in each activity.

Thank you in advance!

[lironbaram]

hi 

this script should work for you 

load *,
     Cost/(round((EndDate-monthstart(StartDate))/30)) as monthlyCost,
     addmonths(monthstart(StartDate),iterno()) as Date
while  addmonths(monthstart(StartDate),iterno())<=EndDate;
load *inline [ 
Activity,Cost,StartDate,EndDate
A,100,16/07/2020,01/12/2020
B,200,17/07/2020,06/05/2021
];

[Enriquemmesteo]

Hi LironBaram,

First of all, thank you for your answer.

Does this script work for you? For me it doesn't show the data..removing the while statement it loads Activity, Cost,EndDate and StartDate...but not Date and monthlyCost

[Enriquemmesteo]

Got this working for the example, just had to change the format of the StartDate and EndDate to look like this in the Inline:

LOAD * INLINE
[Activity, Cost, StartDate, EndDate
A, 100, 2020-07-16, 2020-09-16
B, 200, 2020-07-16, 2021-11-10
];

Now the real data load looks something like this (simplified)...

FOR each File in filelist('*/20*_exampledata*.xls*') /*we update this file every month and name it with the date as prefix(2020.07_exampledata for example) as we do with all the other files in the app*/
Concatenate (Master)
LOAD//here I will load just some of the fields, there ara many more
Activity,

Date("EndDate",'DD.MM.YYYY') AS "EndDate" ,

Date#(if("EndDate"<>NULL(),Year("EndDate")&'.'&num(Month("EndDate"),'00')),'YYYY.MM') AS Date,

Date#(Left(filename(),7), 'YYYY.MM') as StartDate,

Cost

FROM [$(File)]
(ooxml, embedded labels, table is Data);

NEXT File
Can it be implemented in this context?

[Enriquemmesteo]

Hi again @lironbaram , solved it by changing the format of the dates in the inline:

LOAD * INLINE
[Activity, Cost, StartDate, EndDate
A, 100, 2020-07-16, 2020-09-16
B, 200, 2020-07-16, 2021-11-10
];

Now here is a simplified version of the real load:

FOR each File in filelist('*20*_Examplefile*.xls*') //we have all files named with a date prefix like 2020.05
Concatenate (EXAMPLETABLE)
LOAD//will load just some fields, but we have many more
Activity,

Date#(Left(filename(),7), 'YYYY.MM') as StartDate,

Date("EndDate",'DD.MM.YYYY') AS "EndDate" ,

Date#(if("EndDate"<>NULL(),Year("EndDate")&'.'&num(Month("EndDate"),'00')),'YYYY.MM') AS Date,//this is the date common for all data in the app

Cost
FROM [$(File)]
ooxml, embedded labels, table is EXAMPLE);

NEXT File
//****************************can this be implemented?

[lironbaram]

hi 

what i would have done , is loading all the data from all your files 

and than adding another part to the script , that runs the while part on the full data table 

[Enriquemmesteo]

Hi @lironbaram , can you explain a bit more about this?