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)
Please ekskuse my Norglish and Swenglish typos.

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?
Please ekskuse my Norglish and Swenglish typos.
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?
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.
Please ekskuse my Norglish and Swenglish typos.
Highlighted
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)
Please ekskuse my Norglish and Swenglish typos.
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?