Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

divya_anand
Contributor II

Assign calculated values per month between 2 dates

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.

CapacityData.PNGData in QVWCapacity.PNGDesired Calculation

Labels (2)
2 Solutions

Accepted Solutions
Partner
Partner

Re: Assign calculated values per month between 2 dates


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)
Plees ekskuse my Swenglish and or Norweglish spelling misstakes

Re: Assign calculated values per month between 2 dates

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;
16 Replies

Re: Assign calculated values per month between 2 dates

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

divya_anand
Contributor II

Re: Assign calculated values per month between 2 dates

Hi @sunny_talwar ,

I've attached the file now.

Partner
Partner

Re: Assign calculated values per month between 2 dates

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?
Plees ekskuse my Swenglish and or Norweglish spelling misstakes
divya_anand
Contributor II

Re: Assign calculated values per month between 2 dates

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?
Highlighted
Partner
Partner

Re: Assign calculated values per month between 2 dates

Very clear, I assumed another date format D/M/YYYY which gave me 1 day in March and 2 days in April.
Plees ekskuse my Swenglish and or Norweglish spelling misstakes
divya_anand
Contributor II

Re: Assign calculated values per month between 2 dates

So, any ideas?
Partner
Partner

Re: Assign calculated values per month between 2 dates


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)
Plees ekskuse my Swenglish and or Norweglish spelling misstakes
divya_anand
Contributor II

Re: Assign calculated values per month between 2 dates

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
Contributor II

Re: Assign calculated values per month between 2 dates

Hi @sunny_talwar ,

Any idea on the above post?