Maximum Date For Trigger on Sheet

[Anonymous]

I have a trigger on the Sheet level to automatically default to the maximum week.  This trigger is:

='='& Max(Date(Monday_Work_Week))

The expression appears in the Current Selection as seen above.  However, the maximum week is not selected (see image below).

I noticed that the date format is different.  The format in the Current Selection is 03-19-2018 for the Monday_Week_Work.  I date format for the Monday_Week_Work field is 2008-03-19.  Could this be why? If yes, how can I fix it.

[Anonymous]

It is done in the Edit Script.

[sunny_talwar]

So, this is what happens

Now what you need to do is to help Qlik interpret your date as a date field using Date#() function

[Anonymous]

I tried the following:

date(date#(c.StartOfWorkWeek,'YYYY_DD_MM'),'MM/DD/YYYY') Monday

but got the following error message:

ErrorSource: Microsoft OLE DB Provider for SQL Server, ErrorMsg: 'date#' is not a recognized built-in function name.

SQL select

[sunny_talwar]

not in the SQL... you need to try that in Load statement

[Anonymous]

I was able to convert the Monday_Work_week as a date field.  However,  the trigger does not work.

=Date(Max({1} Monday_Work_Week), 'MM/DD/YYYY')

[sunny_talwar]

Try this

=Date(Max({1} Monday_Work_Week), 'YYYY-MM-DD')

[Anonymous]

That works but I would like the format to be 'MM/DD/YYYY' instead of 'YYYY-MM-DD'

[sunny_talwar]

What script did you use to format your date in the back end?

[Anonymous]

convert(varchar(10), cast (Replace(c.StartOfWorkWeek, 'MON - ', '') as Date),101) as Monday_Work_Week

[sunny_talwar]

May be further modify this in Load as I don't really know how this work in SQL

Date(Monday_Work_Week, 'MM/DD/YYYY') as Monday_Work_Week

and then this

=Date(Max({1} Monday_Work_Week), 'MM/DD/YYYY')