topic Assign calculated values per month between 2 dates in QlikView

Assign calculated values per month between 2 dates

divya_anand — Sat, 16 Nov 2024 03:58:02 GMT

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

Re: Assign calculated values per month between 2 dates

sunny_talwar — Wed, 03 Apr 2019 12:42:00 GMT

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

Re: Assign calculated values per month between 2 dates

divya_anand — Thu, 04 Apr 2019 03:45:03 GMT

Hi @sunny_talwar ,

I've attached the file now.

Re: Assign calculated values per month between 2 dates

Vegar — Thu, 04 Apr 2019 04:22:25 GMT

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?

Re: Assign calculated values per month between 2 dates

divya_anand — Thu, 04 Apr 2019 04:29:49 GMT

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?

Re: Assign calculated values per month between 2 dates

Vegar — Thu, 04 Apr 2019 04:45:37 GMT

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

Re: Assign calculated values per month between 2 dates

divya_anand — Thu, 04 Apr 2019 05:00:03 GMT

So, any ideas?

Re: Assign calculated values per month between 2 dates

Vegar — Thu, 04 Apr 2019 05:10:45 GMT

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)

Re: Assign calculated values per month between 2 dates

divya_anand — Thu, 04 Apr 2019 06:27:32 GMT

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.

Re: Assign calculated values per month between 2 dates

divya_anand — Thu, 04 Apr 2019 10:25:54 GMT

Hi @sunny_talwar ,

Any idea on the above post?

Re: Assign calculated values per month between 2 dates

sunny_talwar — Thu, 04 Apr 2019 11:23:45 GMT

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

Re: Assign calculated values per month between 2 dates

divya_anand — Thu, 04 Apr 2019 11:27:15 GMT

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

Re: Assign calculated values per month between 2 dates

sunny_talwar — Thu, 04 Apr 2019 11:34:03 GMT

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

Re: Assign calculated values per month between 2 dates

divya_anand — Thu, 04 Apr 2019 11:55:11 GMT

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?

Re: Assign calculated values per month between 2 dates

sunny_talwar — Thu, 04 Apr 2019 11:57:00 GMT

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;

Re: Assign calculated values per month between 2 dates

divya_anand — Thu, 04 Apr 2019 12:09:10 GMT

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.

Re: Assign calculated values per month between 2 dates

sunny_talwar — Thu, 04 Apr 2019 12:23:47 GMT

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.