topic Re: Maximum Date For Trigger on Sheet in QlikView

Maximum Date For Trigger on Sheet

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Maximum Date For Trigger on Sheet

Anonymous — Thu, 29 Mar 2018 00:07:58 GMT

The Monday_Work_Week is:

cast (Replace(c.StartOfWorkWeek, 'MON - ', '') as Date) as Monday_Work_Week

Re: Maximum Date For Trigger on Sheet

sunny_talwar — Thu, 29 Mar 2018 00:39:05 GMT

Try this

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

Re: Maximum Date For Trigger on Sheet

shiveshsingh — Thu, 29 Mar 2018 07:01:03 GMT

=Date(max(Monday_Week_Work),'YYYY-MM-DD')

Re: Maximum Date For Trigger on Sheet

sasiparupudi1 — Thu, 29 Mar 2018 09:58:38 GMT

Trigger expression

=Max(Monday_Week_Work)

=Date(Max(Monday_Week_Work),'MM-DD-YYYY')

Re: Maximum Date For Trigger on Sheet

sunny_talwar — Thu, 29 Mar 2018 11:16:28 GMT

Or may be this if you want to select max irrespective of any selections

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

Re: Maximum Date For Trigger on Sheet

Anonymous — Thu, 29 Mar 2018 14:53:51 GMT

I have changed the Monday_Work_Week field to have the format MM/DD/YYYY. I tried your code like this but it does not display anything =Date(Max({1} Monday_Work_Week), 'MM/DD/YYYY')

Re: Maximum Date For Trigger on Sheet

sunny_talwar — Thu, 29 Mar 2018 14:59:26 GMT

Your date is not longer understood as date field by Qlik... See how it left aligned

You need to make sure that your date is read properly to make this work

Why don’t my dates work?

QlikView Date fields

Get the Dates Right

Re: Maximum Date For Trigger on Sheet

Anonymous — Thu, 29 Mar 2018 15:04:09 GMT

This is how I got the Monday_Work_Week:

(Replace(c.StartOfWorkWeek, 'MON - ', ''))Monday_Work_Week

I also tried convert and cast without success.

The trigger is still

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

Re: Maximum Date For Trigger on Sheet

sunny_talwar — Thu, 29 Mar 2018 15:05:41 GMT

Is this something you did in QlikView script (under load) or in SQL script (under select)?

Re: Maximum Date For Trigger on Sheet

Anonymous — Thu, 29 Mar 2018 15:07:57 GMT

It is done in the Edit Script.

Re: Maximum Date For Trigger on Sheet

sunny_talwar — Thu, 29 Mar 2018 15:09:46 GMT

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

Re: Maximum Date For Trigger on Sheet

Anonymous — Thu, 29 Mar 2018 15:16:40 GMT

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

Re: Maximum Date For Trigger on Sheet

sunny_talwar — Thu, 29 Mar 2018 15:17:50 GMT

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

Re: Maximum Date For Trigger on Sheet

Anonymous — Thu, 29 Mar 2018 15:27:48 GMT

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')

Re: Maximum Date For Trigger on Sheet

sunny_talwar — Thu, 29 Mar 2018 15:28:40 GMT

Try this

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

Re: Maximum Date For Trigger on Sheet

Anonymous — Thu, 29 Mar 2018 15:30:21 GMT

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

Re: Maximum Date For Trigger on Sheet

sunny_talwar — Thu, 29 Mar 2018 15:31:33 GMT

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

Re: Maximum Date For Trigger on Sheet

Anonymous — Thu, 29 Mar 2018 15:35:23 GMT

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

Re: Maximum Date For Trigger on Sheet

sunny_talwar — Thu, 29 Mar 2018 15:36:46 GMT

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')