Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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

Note:Date is in dd/mm/yyyy format.

1 Solution

Accepted Solutions
MK_QSL
MVP
MVP

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;

View solution in original post

5 Replies
datanibbler
Champion
Champion

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
Author

Hi,

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

MK_QSL
MVP
MVP

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;

maxgro
MVP
MVP

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
Author

Thank you Manish.