# QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

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. Data in QVW Desired Calculation

Labels (2)

• ### Intervals

2 Solutions

Accepted Solutions 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)
???
Celebrating my 300 accepted solution in the Qlik Community
??? MVP

## 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 MVP

## Re: Assign calculated values per month between 2 dates

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

Highlighted
Contributor II

## Re: Assign calculated values per month between 2 dates

I've attached the file now. 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?
???
Celebrating my 300 accepted solution in the Qlik Community
???
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

## 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.
???
Celebrating my 300 accepted solution in the Qlik Community
???
Contributor II

## Re: Assign calculated values per month between 2 dates

So, any ideas? 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)
???
Celebrating my 300 accepted solution in the Qlik Community
???
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.

Contributor II

## Re: Assign calculated values per month between 2 dates

Any idea on the above post?