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.
Solved! Go to Solution.
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;
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?
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.