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: 
Not applicable

Create End of Month date for each month of current year

Hi People,

How do I generate a variable for End of Month for Current Year and each month. Something like this:

Date('Jan' & 'vCurrentYear') so I want Jan through Dec. I think MMM-YYYY will return the End of Month won't it?

Please assist me if you can.

Thank you,

OnePuttGirl

2 Replies
MayilVahanan

Hi

MonthEnd(DateField) gives the end date

ex:

MonthEnd(today()) gives 10/31/2012

For MMM-YYYY format:

=Date(MonthEnd(Today()),'MMM-YYYY')

Hope that helps

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
swuehl
MVP
MVP

Yes, monthend() is probably part of the solution. Do you want to create a field with 12 values, one for each month in current year?

Maybe like

Let vCurrentYear = 2012;


LOAD

Date(floor(monthend(makedate($(vCurrentYear),recno() )))) as MonthEnd

autogenerate 12;

Please note that monthend will return a timestamp for the last millisecond for that day, so if you want to compare that value to a Date numerically, you need to use floor / daystart.

You can also do it like:

Let vCurrentYear = 2012;

LOAD

addmonths(makedate($(vCurrentYear),1,31),recno()-1,1) as MonthEnd

autogenerate 12;