topic Max Date in QlikView

Max Date

tmumaw — Thu, 30 Jul 2015 19:31:37 GMT

Need a little help. I have converted the format of my service date from MM/DD/YYYY to YYYY-MM-DD in my script using the following: Date(Service_Date,'YYYY-MM-DD') as Serv_Date . Then use the following expression: Date(Max(Serv_Date)) it returns MM/DD/YYYY format even though I am using the one I converted in the script by using the following : YYY-YMM-DD. Any ideas?

Re: Max Date

tmumaw — Thu, 30 Jul 2015 19:35:08 GMT

Never mind. I went to the numeric tab and changed the format there, but it makes no sense why it would not take the format I gave it in the script.

Re: Max Date

Kushal_Chawda — Thu, 30 Jul 2015 19:41:47 GMT

When you use the max function it converts the date into Numeric format over which you have applied date function. So Date function will convert it into the default format which is define in the script.

So change the set variable of Date

SET DateFormat='YYY-YMM-DD';

Re: Max Date

maxgro — Thu, 30 Jul 2015 23:30:24 GMT

there is a default format at the start of every script

SET DateFormat='.............';

the date function

date(somefield) will use that format

if you want to change the default format, change the

SET DateFormat ='YYYY-MM-DD';

if you only want to change the format in some part of the script use the second parameter in date function

date(somefield, 'YYYY-MM-DD')

Re: Max Date

jagan — Fri, 31 Jul 2015 01:10:59 GMT

Hi Thom,

You have set the Default date format in script to

SET DateFormat='YYY-MMM-DD';

then only it works. By default if you use any date functions it returns in the above set date format. If you want other than this you have to specify the Date format using Date().

Regards,

Jagan.

Re: Max Date

Anonymous — Fri, 31 Jul 2015 01:14:02 GMT

Hi,

SET DateFormat ='YYYY-MM-DD';

Re: Max Date

qlikviewwizard — Fri, 31 Jul 2015 02:16:42 GMT

Please use below script:

SET DateFormat='M/DD/YYYY';

SET TimestampFormat='M/DD/YYYY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

Table:

LOAD date(Serv_Date) as Serv_Date Inline [

Serv_Date

6/25/2010

1/29/2011

12/21/2013

12/21/2014

06/21/2015

06/21/2013

]

;

left Join (Table)

LOAD

max(date(Serv_Date)) as Max_Date

Resident Table;

EXIT Script;