topic Re: Dates and Calendars in QlikView

Dates and Calendars

Wed, 25 Nov 2020 16:16:04 GMT

I appreciate there are many, many discussions on here about this subject, but having spent half a day trawling through them I am still at a loss!

I have two calendar objects, which are linked to variables, and in the Min and Max Value I have entered =min({1} DATE(OCR_DATE_TIME_SENT_TO_OCR, 'DD/MM/YYYY')) and =MAX({1} DATE(OCR_DATE_TIME_SENT_TO_OCR, 'DD/MM/YYYY')) . the number format on both objects is DD/MM/YYYY.

I have formatted the field OCR_DATE_TIME_SENT_TO_OCR in the load script like this:Date(DATE_TIME_SENT_TO_OCR, 'DD/MM/YYYY') as OCR_DATE_TIME_SENT_TO_OCR - this works as when I use this field in a table or simply add it to the sheet, it appears in this format without any need to amend the number forma

have set the Date format as

SET DateFormat='DD/MM/YYYY';

But when I pick from my calendars, nothing is selected. And in the variable overview, the definition of my variables is in this format:

42775.753472222

So I can see that the date formats are not the same, but I cannot see why the variable is still being populated with numbers. I even tried removing formatting from everything, and it still wouldn't select anything!

All help appreciated

Ella

Re: Dates and Calendars

adamdavi3s — Thu, 30 Mar 2017 09:55:51 GMT

Hi Ella,

The trick is to floor the date here, its always a pain!

try something like this: DATE(floor(min({1}OCR_DATE_TIME_SENT_TO_OCR)), 'DD/MM/YYYY'))

possibly this, just experiment with it!

MIN(NUM(DATE(FLOOR(Timestamp#(OCR_DATE_TIME_SENT_TO_OCR)),'DD/MM/YYYY')))

Re: Dates and Calendars

Thu, 30 Mar 2017 10:01:15 GMT

Sorry, still doesn't select anything, although the format in the variable definition has now changed to be just 42775

Re: Dates and Calendars

adamdavi3s — Thu, 30 Mar 2017 10:04:18 GMT

Hi Ella,

Can you upload a sample at all? Sorry hard to picture it without it in front of me

Which formula did you use in the end? if it was the latter then wrapping it in another date might work

DATE(MIN(NUM(DATE(FLOOR(Timestamp#(OCR_DATE_TIME_SENT_TO_OCR)),'DD/MM/YYYY'))),'DD/MM/YYYY')

Re: Dates and Calendars

Thu, 30 Mar 2017 10:18:41 GMT

I can't see a link to upload a qv file - is it me?!

Re: Dates and Calendars

adamdavi3s — Thu, 30 Mar 2017 10:20:02 GMT

you have to go to 'use advanced editor' then use the attach / paperclip icon down the bottom

Re: Dates and Calendars

Thu, 30 Mar 2017 10:29:24 GMT

thanks! Have restricted it down to just a few months to keep it small - hope it helps!

Re: Dates and Calendars

adamdavi3s — Thu, 30 Mar 2017 10:32:56 GMT

Perfect

Ok so what is the plan, just get something to show you the dates between your two selections?

Re: Dates and Calendars

adamdavi3s — Thu, 30 Mar 2017 10:36:30 GMT

Ok so I tried this formula in a table with the ocr date as a dimension and it worked OK for me

if(OCR_DATE_TIME_SENT_TO_OCR >= $(vStartDate) and OCR_DATE_TIME_SENT_TO_OCR <=$(vEndDate),1,0)

Re: Dates and Calendars

Thu, 30 Mar 2017 10:36:44 GMT

That's it - when it works, I would expect the date fields at the top be impacted by the selections, and the selections themselves to appear in the 'Selections' box.

Re: Dates and Calendars

adamdavi3s — Thu, 30 Mar 2017 10:39:28 GMT

Ahhh ok, hmmm this isn't quite so easy I am afraid!

Sorry just for my own clarification (not had much sleep)

You want users to be able to enter a start date then an end date and for this to then make selections in the calendar?

This is possible but I would advise a button to do it....

Re: Dates and Calendars

Thu, 30 Mar 2017 10:46:09 GMT

I do need users to be able to enter a start and end date - we use calendars in other applications without issues, I did toy with a button, but the users are used to calendars (and you know users... change is not a good thing )

Re: Dates and Calendars

adamdavi3s — Thu, 30 Mar 2017 10:51:25 GMT

The attached uses a button, but I have also added a variable trigger for you.

Just try changing the end date variable and watch the magic work

Sorry I am crazy busy so I have only added it on the venddate variable but you can just add the same on the vstartdate variable.

Settings> Document properties > triggers then look at the variable triggers section.

You need both on input and on change to make sure it always works.

Re: Dates and Calendars

Thu, 30 Mar 2017 11:04:54 GMT

the trigger was missing!! thank you for your help and time