Assign calculated values per month between 2 dates

[divya_anand]

Hi,

I have created a sample QVW attached to this post with a simple table (as shown in below screenshot). I am trying to create a logic where I would have to divide the "Hrs" equally among the months between the Start & End Date (As shown in next screenshot). Anybody has an idea how I can do it at script level. 

Thank you.

Data in QVWDesired Calculation

[sunny_talwar]

@divya_anand Don't see the qvw attached. Can you check and attach it again

[divya_anand]

Hi @sunny_talwar ,

I've attached the file now.

[Vegar]

What du you mean by equally?
Imagine you have 150 hours to divide between the dates 31/3/2019 and 2/4/2019. How many hours would you like to see assigned to March 2019? Is it 50 or 75 hours?

[divya_anand]

Hi,
The start date & end date is always going to be 1st of the month as in the screenshots attached above. So, considering your example to be 3/1/2019 (M/D/YYYY) & 4/1/2019, we have 2 months between these start & end dates (inclusive). Hence, divide the Hrs (150) by 2 = 75. So, Mar will be assigned 75 & Apr =75.
Is this clear?

[Vegar]

Very clear, I assumed another date format D/M/YYYY which gave me 1 day in March and 2 days in April.

[divya_anand]

So, any ideas?

[Vegar · Accepted solution]

I would probably do an left join intervalmatch against a table listing all possible months.
Then
Left join count(month), project
Resident Table
Group by project;

At last do a calculation where you divide the hours with the count.

(I hope you get something from this description, I'm typing on my mobile)

[divya_anand]

This worked. Thank you very much.

I have another challenge, attaching the working QVW below.

Instead of assigning equally (Hrs/No of months), Is it possible to assign 60% hrs to 1st 33% months, 25% to next 33% months & remaining 15% hrs to the remaining 33% months?

For eg. 

Hrs=192;

Start Date: 6/1/2018;

End Date: 6/1/2019;

Hence No. of Months=13

13(No. of Months) divided by 3 (always a standard, 3 phases)= 4.33, floor(4.33)= 4

Hence 4 months in each phase.

Assign 60% of 192 (Hrs) = 115.2 to 1st 4 months (2018 Jun=115.2/4= 28.8, Jul=28.8, Aug=28.8, Sep=28.8)

25% of 192 = 48 to next 4 months (2018 Oct=48/4=12, Nov=12, Dec=12, 2019 Jan=12)

15% of 192 = 28.8 to last 4 months(2019 Feb= 28.8/4=7.2, Mar=7.2, Apr=7.2, May=7.2)

I am working on this, if an idea strikes your mind, please give me a hint. Thank you.

[divya_anand]

Hi @sunny_talwar ,

Any idea on the above post?