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

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

Yes ma'am

Try this

Left Join(Data)
%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

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

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

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