Qlik Community

QlikView Deployment

Discussion Board for collaboration related to QlikView Deployment.

pkpandey
New Contributor II

Last Date Of the Month

Hi All

How to get the last date of the month in the expression Max({<Year={$(=Max(Year))},Month={$(=Month(Max(TranDate)))}>} TranDate)

where TranDate is in DD/MM/YYYY format and I need the last date of the month also in the same format.

Tags (1)
Labels (1)
1 Solution

Accepted Solutions

Re: Last Date Of the Month

Monthend returns a timestamp, not a date.


talk is cheap, supply exceeds demand
13 Replies
MVP
MVP

Re: Last Date Of the Month

May be this:

Date(Max({<Year={$(=Max(Year))},Month={$(=Month(Max(TranDate)))}>} TranDate), 'DD/MM/YYYY')

Re: Last Date Of the Month

Date(MonthStart(max(TranDate),1)-1,'DD/MM/YYYY')


talk is cheap, supply exceeds demand
MVP
MVP

Re: Last Date Of the Month

Although I wonder if this is working also:

Date(Max({<TransDate>}TransDate), 'DD/MM/YYYY')

sureshqv
Esteemed Contributor III

Re: Last Date Of the Month

add date to ur exp?

sureshqv
Esteemed Contributor III

Re: Last Date Of the Month

chk dismonthend.png

MVP
MVP

Re: Last Date Of the Month

Gysbert is there a reason you are first calculating the next month's start and then subtracting 1 to get month end? Is there a reason not to use Date(MonthEnd(Max(TranDate)),'DD/MM/YYYY')

Re: Last Date Of the Month

Monthend returns a timestamp, not a date.


talk is cheap, supply exceeds demand
MVP
MVP

Re: Last Date Of the Month

Hi,

Try like this

Date(Max({<Year={'$(=Max(Year))'},Month={'$(=Month(Max(TranDate)))'}, TranDate=>} TranDate), 'DD/MM/YYYY')


Hope this helpsyou.


Regards,

jagan.

MVP
MVP

Re: Last Date Of the Month

Isn't that true for MonthStart as well? I though it returned a TimeStamp as well. I might be totally wrong and it would be a good to know kind of thing