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 wrote:

Start Date: 6/1/2018;

End Date: 6/1/2019;

Hence No. of Months=13

---

How is no of months = 13 here? From a date in 2018 to the same date in 2019, shouldn't it be just 12 months flat? I am confused?

[divya_anand]

Hi Sunny,
it is inclusive of both 2018-Jun & 2019-Jun.

[sunny_talwar]

Would you be able to give another example... I guess for project B to show how that one will play out?

[divya_anand]

Sure, So with Project B we have
Hrs=1683
Start: 3/1/2018
End: 7/1/2019
Hence, no of months = 17

17(No. of Months) divided by 3 (always a standard, 3 phases)= 5.66, floor(5.66)= 5

Hence 5 months in each phase. 5 months for 3 phases is going to sum up to 15 only (not 17), but it's ok.

Assign 60% of 1683 (Hrs) = 1009.8 to 1st 5 months (2018 Mar=1009.8/5= 201.96, Apr=201.96, May=201.96, Jun=201.96, Jul=201.96)

25% of 1683 = 420.75 to next 5 months (2018 Aug=420.75/5=84.15, Sep=84.15, Oct=84.15, Nov=84.15, Dec=84.15 )

15% of 1683 = 252.45 to last 5 months(2019 Jan= 252.45/5=50.49, Feb= 50.49 , Mar=50.49, Apr=50.49, May=50.49)

is it clear?

[sunny_talwar · Accepted solution]

Yes ma'am

Try this

Left Join(Data)
Load Project,
	 %DATE_KEY,
	 If(MonthStart(Start, Floor(Only(MonthsDiff)/3)) > %DATE_KEY, Only(Hrs) * 0.60,
	 	If(MonthStart(Start, Floor(Only(MonthsDiff)/3)*2) > %DATE_KEY, Only(Hrs) * 0.25, Only(Hrs) * 0.15))/Floor(Only(MonthsDiff)/3) as Value
Resident Data
GROUP BY Project, %DATE_KEY, Start;

[divya_anand]

Sunny,
I probably took more time to explain the problem to you than the time you took to give me a solution. It's fantastic. Thank you so much.

[sunny_talwar]

I think a good explanation is part of getting to the correct answer quickly. A lot of the time people just think that they can put anything out there and can expect to get a response which will work for them. So, kudos to you for spending time to go over in details as to what you have and what you needed.