Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Now Live: Qlik Sense SaaS Simplified Authoring – Analytics Creation for Everyone: READ DETAILS
Showing results for 
Search instead for 
Did you mean: 
Creator III
Creator III

Assign calculated values per month between 2 dates


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 QVWData in QVWDesired CalculationDesired Calculation

Labels (2)
16 Replies

@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?

Creator III
Creator III

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

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

Creator III
Creator III

Sure, So with Project B we have
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?

Yes ma'am

Try this

Left Join(Data)
Load Project,
	 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;
Creator III
Creator III

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.

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.