Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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

NoteSmiley Very Happyate is in dd/mm/yyyy format.

1 Solution

Accepted Solutions
MK_QSL
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
datanibbler
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.

MK_QSL
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;

maxgro
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.