Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
apthansh
Creator
Creator

Calculate number of months occurring in current year only

Hi,

I have 2 date columns

Start Date and End Date

ID   Start date    End date

1    1-Nov-16     31-Jul-17

2    1-Aug-17     31-Jul-18

Here I have to calculate number of months occurring in 2017 only.Output should be as below

ID   Start date    End date   NoOfMonths

1    1-Nov-16     31-Jul-17     7                          (b/w Nov-2016 and Jul 2017  total  7 months are in 2017)

2    1-Aug-17     31-Jul-18     5                          (b/w Aug-2017 and Jul 2018  total   5 months are in 2017)

How can I put this in script ?

Thank you very much

1 Solution

Accepted Solutions
sunny_talwar

May be this:

Table:

LOAD *,

  If([Start date] >= MakeDate(Year(Today())),

  If([End date] < MakeDate(Year(Today()) + 1), Month([End date]) - Month([Start date]), 12 - Month([Start date]) + 1),

  If([End date] < MakeDate(Year(Today()) + 1), Num(Month([End date])), 12)) as NoOfMonths;

LOAD * INLINE [

    ID,  Start date,    End date

    1,    1-Nov-16,    31-Jul-17

    2,    1-Aug-17,    31-Jul-18

];

View solution in original post

5 Replies
sunny_talwar

May be like this

Num(Month([End date])) as NoOfMonths

adamdavi3s
Master
Master

=((year([End date])*12)+month([End date])) - (((year([Start date])*12)+month([Start date])))


This basically creates a numeric 'period' for each date and takes one from the other


whoops didn't notice the 'only in 2017 bit'


=

(if([End date] > yearend(Today()), (((year(yearend(Today()))*12)+month(yearendt(Today())))),(((year([End date])*12)+month([End date]))))

-

(if([Start date] < yearstart(Today()), (((year(yearstart(Today()))*12)+month(yearstart(Today())))),(((year([Start date])*12)+month([Start date]))))

sunny_talwar

May be this:

Table:

LOAD *,

  If([Start date] >= MakeDate(Year(Today())),

  If([End date] < MakeDate(Year(Today()) + 1), Month([End date]) - Month([Start date]), 12 - Month([Start date]) + 1),

  If([End date] < MakeDate(Year(Today()) + 1), Num(Month([End date])), 12)) as NoOfMonths;

LOAD * INLINE [

    ID,  Start date,    End date

    1,    1-Nov-16,    31-Jul-17

    2,    1-Aug-17,    31-Jul-18

];

apthansh
Creator
Creator
Author

Thank you Sunny.Awesomeness...You are my hero

vinieme12
Champion III
Champion III

or just this

LOAD

*,

if(Year(Startdate)-Year(Enddate)=0,Month(Enddate)-Month(Startdate),if(Year(Startdate)=Year(Today()),12-Month(Startdate),Month(Enddate)-1)+1) as NoOfMonth;

LOAD ID,date(Date#(Startdate,'DD-MMM-YY'),'DD/MM/YY') as Startdate,date(Date#(Enddate,'DD-MMM-YY'),'DD/MM/YY') as Enddate Inline [

ID,Startdate,Enddate

1,1-Nov-16,31-Jul-17

2,1-Aug-17,31-Jul-18

];

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.