Calculation in load script

[Pomna]

Hello everyone,

I need help creating a load script for an app with calculated columns. Attached is sample data

The calculated columns are [Days for Calculation],  [Daily Rate], and [Monthly Fee].

[Daily Rate] is [Fee Monthly Rate] divided by [Days for calculation]. 

[Monthly Fee] is [Days for calculation multiplied by [Daily Rate].

Below are the four possible scenarios to get [Days for Calculation]:

1 - If start date is  this month and end next month or later - in that case we subtract the number of days in the month from the start date and then add 1. So if they started on March 30, we would take 31 (the number of days in the month) and subtract 30 (the day they started) and then add 1 for a total of 2.

2 - If start date is  prior month and end next month or later - just take the number of days in the month - for March it would be 31

3 - If start date is  prior month and end sometime this month. Take the day they are leaving and subtract 1. For example, end date of March 5. Take 5 and subtract 1 for a total of 4.

4 - If start date is  this month and end sometime this month. Subtract the day they end from the day they start. For example - Start March 3, end March 5. 5-3=2

Thanks for the suggestions.

[morgankejerhag]

Do you want to split the costs into the months they belong to? In this case you can use intervalmatch() to either match dates or months.

[Pomna]

Thanks for the response but this gives me little to go by. I need a load script that would 

1.calculate the [Days for Calculation] column according to the four scenarios mentioned above.

2.   calculate [Daily Rate] which is [Fee Monthly Rate] divided by [Days for Calculation]

3. Calculate [Monthly Fee] which is [Daily Rate] multiplied by [Days For Calculation].