Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Extracting months between 2 dates

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

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

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

View solution in original post

11 Replies
Anonymous
Not applicable
Author

Just to see if i got it,

>= Start Month and  <= End date?

Anonymous
Not applicable
Author

Yes

Anonymous
Not applicable
Author

SQL function on you load script:

datediff(mm,StartDate,EndDate)

And Add 2 to it, right?

Anonymous
Not applicable
Author

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

Ajuda.PNG

Anonymous
Not applicable
Author

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

Anonymous
Not applicable
Author

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

Anonymous
Not applicable
Author

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

Anonymous
Not applicable
Author

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;

Anonymous
Not applicable
Author

Hey Raffaele,

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