Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

juanagustin
New Contributor

Last day of the month

Hi, I have this field

Sin título.png

I want to put the last day of each Month, for example: 20171231

Can you help me? Thanks!

9 Replies
clondono
Contributor III

Re: Last day of the month

Try using MonthEnd() function

=Date(MonthEnd([USR_FCRMVH_MESCER]))

Regards,

Carlos

clondono
Contributor III

Re: Last day of the month

You can also add date formatting to return the date in any format you like:

=Date(MonthEnd([USR_FCRMVH_MESCER]))

Will return:          '12/31/2017'


=Date(MonthEnd([USR_FCRMVH_MESCER]), 'YYYYMMDD')

Will return:          '20171231'

hdbucher
New Contributor III

Re: Last day of the month

Try this Sample:

=date(MonthEnd(makedate(mid(201707,1,4), mid(201703,5,2))),'YYYYMMDD')

or

=date(MonthEnd(makedate(left(201707,4), Right(201703,2))),'YYYYMMDD')

=date(MonthEnd(makedate(mid(USR_FCRMVH_MESCER,1,4), mid(USR_FCRMVH_MESCER,5,2))),'YYYYMMDD')

jomar_ebonite
New Contributor III

Re: Last day of the month

Hi Juan,

Try this:

= USR_FCRMVH_MESCER & MonthEnd(Date#(USR_FCRMVH_MESCER,'YYYYMM'))



Regards,

Jomar

Re: Last day of the month

Date(MonthEnd(Date#(DateFieldName,'YYYYMM')),'YYYYMMDD')

Regards

Regards,
Prashant Sangle
MVP
MVP

Re: Last day of the month

Watch out for MonthEnd() - it will add a time component to your date (23:59:59.999) which Date() hides but does not remove. This can cause all sorts of problems with associations and conditions. Rather use:

=Date(Floor(MonthEnd([USR_FCRMVH_MESCER])), 'YYYYMMDD')

or

=Date(MonthStart([USR_FCRMVH_MESCER], 1), -1), 'YYYYMMDD')

=Date(MonthStart([USR_FCRMVH_MESCER], 1) - 1, 'YYYYMMDD')


to ensure that you have a 'time-free' date value.

sasiparupudi1
Honored Contributor III

Re: Last day of the month

Date (Floor (Monthend (Date#(yourdate,'YYYYMM'))),'YYYYMMDD')

formosasol
New Contributor

Re: Last day of the month

Jonathan you are missing a ( in the "Date(MonthStart([USR_FCRMVH_MESCER], 1), -1), 'YYYYMMDD')"

Should be "Date((Monthend(Data,1)-1),'DD/MM/YYYY') as LastDayofMonth"

MVP
MVP

Re: Last day of the month

Actually an extra comma. Now fixed.

Community Browser