topic Re: Max date from a string in QlikView

Max date from a string

Thu, 20 Nov 2014 08:39:50 GMT

Hi I have a source file which stores dates as in this format '2000 Jan'.

Now I want to find the latest date so I use Max(datefield)

But all I get is a blank value. Any help?

I am guessing that it could be considering my field as string and hence the max function isn't working..

tresB — Thu, 20 Nov 2014 08:44:52 GMT

Try:

Date(Max(Date#(datefield, 'YYYY MMM')))

jagan — Thu, 20 Nov 2014 08:53:32 GMT

Hi,

Try like this

Date(Max(Date#(DateDimensionName, 'YYYY MMM')))

Hope this helps you.

Regards,

Jagan.

morganaaron — Thu, 20 Nov 2014 08:54:54 GMT

If you need to keep the same format (Year and Month only) you could use either:

MonthName(Max(Date#(Datefield, 'YYYY MMM')))

Year(Max(Date#(Date, 'YYYY MMM'))) & ' ' & Month(Max(Date#(Date, 'YYYY MMM')))

MarcoWedel — Thu, 20 Nov 2014 09:00:18 GMT

MonthName(Max(Date#(Datefield, 'YYYY MMM')))

is the better choice, because it keeps the numerical value, so no sorting or calculation issues.

hope this helps

regards

Marco

jagan — Fri, 21 Nov 2014 02:09:08 GMT

Hi,

If you got the solution please close this thread by giving Correct and Helpful Answers.

Regards,

Jagan.