topic Re: Calculate number of months occurring in current year only in QlikView

Calculate number of months occurring in current year only

apthansh — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Calculate number of months occurring in current year only

sunny_talwar — Wed, 08 Feb 2017 13:03:43 GMT

May be like this

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

Re: Calculate number of months occurring in current year only

adamdavi3s — Wed, 08 Feb 2017 13:07:39 GMT

=((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]))))

Re: Calculate number of months occurring in current year only

sunny_talwar — Wed, 08 Feb 2017 13:14:30 GMT

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

];

Re: Calculate number of months occurring in current year only

apthansh — Wed, 08 Feb 2017 13:21:28 GMT

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

Re: Calculate number of months occurring in current year only

vinieme12 — Wed, 08 Feb 2017 13:25:12 GMT

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

];