# QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Not applicable

## How to calculate the number of months between two dates

Hi,

I have a below requirement.

 From_dt To_dt Value 1/1/2015 1/3/2015 3000 14/1/2015 20/10/2015 20000 7/3/2015 12/5/2015 6000 10/2/2015 10/11/2015 9000

For eg:when I select 1/1/2015 then I need to find the number of months from from_dt to to_dt then distribute the values equally for those months i.e I will consider from Jan to Mar as 3 months and have to display the output as below.

Jan 2015  Feb 2015  Mar 2015

1000          1000           1000

Noteate is in dd/mm/yyyy format.

1 Solution

Accepted Solutions
Not applicable

## Re: How to calculate the number of months between two dates

Test:

Load

*,

Date(MonthStart(From_dt),'MMM YYYY') as FromMonthStart,

Month(From_dt) as FromMonth,

Month(To_dt) as ToMonth,

(Month(To_dt) - Month(From_dt)+1) as IternationNumber;

Load

AutoNumber(From_dt & To_dt) as ID,

Date(MonthStart(From_dt)) as From_dt,

MonthEnd(To_dt) as To_dt,

Value

Inline

[

From_dt, To_dt, Value

1/1/2015, 1/3/2015, 3000

14/1/2015, 20/10/2015, 20000

7/3/2015, 12/5/2015, 6000

10/2/2015, 10/11/2015, 9000

];

Final:

Load

ID,

Date(AddMonths(Date#(FromMonthStart, 'MMM YYYY'),IterNo()-1),'MMM YYYY') as FromMonthStart,

Value/IternationNumber as Value

Resident Test

While FromMonth + IterNo() - 1 <= Month(To_dt);

Drop Table Test;

5 Replies
Not applicable

## Re: How to calculate the number of months between two dates

Hi rekha,

do you want to consider the exact day or only the month that the resp. date is in?

The former would be somewhat complicated as a month does not always have the same nr. of days - how many months would 3 days be (if the to_date is the 3rd of any month)?

The latter would be easy - the month() function gives you that.

HTH

Not applicable

## Re: How to calculate the number of months between two dates

Hi,

I just want to consider the Month irrespective of the date.

Not applicable

## Re: How to calculate the number of months between two dates

Test:

Load

*,

Date(MonthStart(From_dt),'MMM YYYY') as FromMonthStart,

Month(From_dt) as FromMonth,

Month(To_dt) as ToMonth,

(Month(To_dt) - Month(From_dt)+1) as IternationNumber;

Load

AutoNumber(From_dt & To_dt) as ID,

Date(MonthStart(From_dt)) as From_dt,

MonthEnd(To_dt) as To_dt,

Value

Inline

[

From_dt, To_dt, Value

1/1/2015, 1/3/2015, 3000

14/1/2015, 20/10/2015, 20000

7/3/2015, 12/5/2015, 6000

10/2/2015, 10/11/2015, 9000

];

Final:

Load

ID,

Date(AddMonths(Date#(FromMonthStart, 'MMM YYYY'),IterNo()-1),'MMM YYYY') as FromMonthStart,

Value/IternationNumber as Value

Resident Test

While FromMonth + IterNo() - 1 <= Month(To_dt);

Drop Table Test;

Not applicable

## Re: How to calculate the number of months between two dates

a:

load

*,

year(To_dt)*12+month(To_dt) - (year(From_dt)*12+month(From_dt)) +1 as MonthDiff

inline [

From_dt , To_dt, Value

1/1/2015, 1/3/2015 ,3000

14/1/2015, 20/10/2015 ,20000

7/3/2015, 12/5/2015 ,6000

10/2/2015, 10/11/2015 ,9000

];

b:

load

From_dt , To_dt, Value/MonthDiff as NewValue,

AddMonths(MonthStart(From_dt), IterNo()-1)

Resident a

While AddMonths(MonthStart(From_dt), IterNo()-1) <= MonthStart(To_dt) ;

DROP Table a;

Not applicable

## Re: How to calculate the number of months between two dates

Thank you Manish.

Community Browser