Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

agaetisproject
New Contributor III

Calculate number of months between dates in script

Hi there,

Anyone can help me compute the number of months between two dates in the script.

It seems quite obvious but I can't manage to make this work so would appreciate your guidance.

Below an example of what I try to achieve:

- Start Date and End date are part of the original file

- NumberOfMonth is what I try to compute.

Many thanks for your helps!

Start DateEnd DateNumberOfMonth
01/01/201631/12/201612
01/01/201631/07/20167
01/01/201615/08/20168
01/01/201628/02/201714
Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

Re: Calculate number of months between dates in script

source:

load * inline [

Start Date, End Date

01/01/2016, 31/12/2016

01/01/2016, 31/07/2016

01/01/2016, 15/08/2016

01/01/2016, 28/02/2017

];

final:

load

  [Start Date],

  [End Date],

  (Year([End Date])*12 + Month([End Date]))

  -

  (Year([Start Date])*12 + Month([Start Date]))

  + 1 as NumberOfMonth

Resident

  source;

DROP Table source;

4 Replies
MVP
MVP

Re: Calculate number of months between dates in script

Something like

=(Year([End Date])-Year([Start Date]))*12+(Month([End Date])-Month([Start Date]))

MVP
MVP

Re: Calculate number of months between dates in script

source:

load * inline [

Start Date, End Date

01/01/2016, 31/12/2016

01/01/2016, 31/07/2016

01/01/2016, 15/08/2016

01/01/2016, 28/02/2017

];

final:

load

  [Start Date],

  [End Date],

  (Year([End Date])*12 + Month([End Date]))

  -

  (Year([Start Date])*12 + Month([Start Date]))

  + 1 as NumberOfMonth

Resident

  source;

DROP Table source;

Not applicable

Re: Calculate number of months between dates in script

Jeff try below:

(Year(EndDate)*12 + Month(EndDate)) - (Year(StartDate)*12 + Month(StartDate)) + 1

neetha_p
Honored Contributor

Re: Calculate number of months between dates in script

Hi Jeff,

in Script:

Months:

Load

  *,

  (End_Year - Start_Year)*12 + (End_Month - Start_Month) + 1 as NumberOfMonths;

Load

  Year(Start_Date) as Start_Year,

  Year(End_Date) as End_Year,

  Month(Start_Date) as Start_Month,

  Month(End_Date) as End_Month,

  Price

Inline

[

Start_Date,End_Date,Price

01/01/2016,31/12/2016,1000

01/01/2016,31/07/2016,5000

01/01/2016,15/08/2016,8000

01/01/2016,28/02/2017,14000

];

in UI:

=((End_Year - Start_Year)*12 + (End_Month - Start_Month) + 1)

Regards

Neetha

Community Browser