Counting months between startdate and EndDate

[Anonymous]

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

[rwunderlich · Accepted solution]

(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

[maxgro]

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

[Anonymous]

Thank you

The script works perfect

[Anonymous]

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