topic Re: Extracting months between 2 dates in QlikView

Extracting months between 2 dates

Anonymous — Tue, 07 Nov 2017 10:36:53 GMT

Hi all,

I'm curious if there's a way I can extract months between two dates... I can get the number of months between 2 dates, but I want to be able to get the actual month names... For example;

Start.Date = 01/01/2017

End.Date = 31/03/2017

Months between two dates = 3.

Now, I want to output those months in for format of the actual month names,so output should be... Jan, Feb, March...

How can I achieve this?

Cheers

Re: Extracting months between 2 dates

Anonymous — Tue, 07 Nov 2017 10:41:41 GMT

Just to see if i got it,

>= Start Month and <= End date?

Re: Extracting months between 2 dates

Anonymous — Tue, 07 Nov 2017 10:42:58 GMT

Yes

Re: Extracting months between 2 dates

Anonymous — Tue, 07 Nov 2017 10:50:08 GMT

SQL function on you load script:

datediff(mm,StartDate,EndDate)

And Add 2 to it, right?

Re: Extracting months between 2 dates

Anonymous — Tue, 07 Nov 2017 10:58:24 GMT

actually use like This:

Add this to your code:

SET MonthDiff = Num(((year($2) * 12) + month($2)) - (((year($1) * 12) + month($1))) + 1);

Then add, for example:

$(MonthDiff(data_entrega, data_cadastro))+2 as MonthDiff

Re: Extracting months between 2 dates

Anonymous — Tue, 07 Nov 2017 11:11:14 GMT

I believe that will just give the number of months between 2 dates, I am trying to achieve the name of months between 2 dates... As in, Jan, Feb, March etc

Re: Extracting months between 2 dates

Anonymous — Tue, 07 Nov 2017 11:29:31 GMT

Oh, right, i'm not able to it yet, sorry :C

Re: Extracting months between 2 dates

Anonymous — Tue, 07 Nov 2017 11:31:04 GMT

I am still trying myself, thanks for your help so far.

Re: Extracting months between 2 dates

Anonymous — Wed, 08 Nov 2017 21:40:37 GMT

hey there! try this out:

LOAD start_date,

end_date,

month(end_date)-month(start_date) as NofMonths,

month(start_date) as month_start

FROM

months.xlsx

(ooxml, embedded labels, table is Sheet1);

let MonthStart=num(Peek('month_start',0,'months'),00);

let number=Peek('NofMonths',0,'months');

month_names:

LOAD Month(date#(01&num#($(MonthStart),00)&2000,'DDMMYYYY')) as month_name AutoGenerate 1;

for i=1 to $(number)

let vCurMonth=num($(MonthStart)+$(i),00);

Concatenate (month_names)

LOAD Month(date#(01&num#($(vCurMonth),00)&2000,'DDMMYYYY')) as month_name AutoGenerate 1;

NEXT i;

Re: Extracting months between 2 dates

Anonymous — Thu, 09 Nov 2017 09:37:33 GMT

Hey Raffaele,

Thank you that works for extracting the months... Now to connect the remaining months with the month names

Re: Extracting months between 2 dates

Anonymous — Thu, 09 Nov 2017 09:53:37 GMT

you re welcome

here it is a modified version of the previous script, containing the generalization in case of multiple rows and the connection among the remaining months and month names

months:

LOAD start_date,

end_date,

month(end_date)-month(start_date) as NofMonths,

month(start_date) as month_start,

AutoNumber(start_date&end_date) as key,

RowNo() as rows

FROM

months.xlsx

(ooxml, embedded labels, table is Sheet1);

nrows:

LOAD max(rows) as nrows Resident months;

let nrows=Peek('nrows',0,'nrows');

DROP Table nrows;

for nrow = 0 to $(nrows)-1

let MonthStart=num(Peek('month_start',$(nrow),'months'),00);

let vKey=Peek('key',$(nrow),'months');

let number=Peek('NofMonths',$(nrow),'months');

month_names:

LOAD Month(date#(01&num#($(MonthStart),00)&2000,'DDMMYYYY')) as month_name,

$(vKey) as key AutoGenerate 1;

for i=1 to $(number)

let vCurMonth=num($(MonthStart)+$(i),00);

Concatenate (month_names)

LOAD Month(date#(01&num#($(vCurMonth),00)&2000,'DDMMYYYY')) as month_name,

$(vKey) as key AutoGenerate 1;

NEXT i;

next nrow;

Re: Extracting months between 2 dates

fwiegold — Tue, 19 Jun 2018 12:50:04 GMT

It only works for the year 2000. what happens when the months are of different year