topic Counting months between startdate and EndDate in QlikView

Counting months between startdate and EndDate

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

I am building a Qlik-report and I have a problem that I cannot solve. I have tried many different scripts "counting months between two dates". But those scripts aren't exact what I am looking for.

I need a script that can pickup the different of the dates 01, 15 and 16 for both StartDates and EndDates. The example below shows how the months are counting. The script should apply for the whole calendar from January to December

StartDate	EndDate	Count month
01.01.2018	15.06.2018	5
01.01.2018	16.06.2018	6
15.01.2018	15.06.2018	5
15.01.2018	16.06.2018	6
16.01.2018	15.06.2018	4
16.01.2018	16.06.2018	5

I appreciate any helps. Thanks

Re: Counting months between startdate and EndDate

rwunderlich — Thu, 22 Feb 2018 19:42:07 GMT

(Month(EndDate) + fabs(Day(EndDate)>15))

- (Month(StartDate)+ fabs(Day(StartDate)>15))

If you need to cross years, it just needs a small tweak to consider the year.

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

Re: Counting months between startdate and EndDate

maxgro — Thu, 22 Feb 2018 19:50:02 GMT

maybe

SET DateFormat='DD.MM.YYYY';

LOAD

StartDate,

if(day(StartDate) <= 15, MonthStart(StartDate), MonthStart(AddMonths(StartDate,1))) as ds,

EndDate,

if(day(EndDate) <= 15, MonthStart(EndDate), MonthStart(AddMonths(EndDate,1))) as de,

[Count month]

FROM

[https://community.qlik.com/thread/292502]

(html, codepage is 1252, embedded labels, table is @1);

the expression in the chart is

Num(((year(de) * 12) + month(de)) - (((year(ds) * 12) + month(ds))) + 1) -1

Re: Counting months between startdate and EndDate

Anonymous — Thu, 22 Feb 2018 20:52:20 GMT

Thank you

The script works perfect

Re: Counting months between startdate and EndDate

Anonymous — Thu, 22 Feb 2018 20:53:09 GMT

The script of Rob Wunderlich is easier and shorter to use in my script.

Thank you for the solution. I will try your script in my report