Qlik Sense not interpreting the year number as the actual year

[TimmyCNHi]

Hi everyone

I find this one of the most frustrating aspects of Qlik: date formats!

In my source data I have a table with a column called Date_A which has the following format:

11/26/2019 12:00:00 AM

I added a calculated field with the following expression:

Date(Date_A,'YYYY')

Also tried (same result):

Date(Floor(Date_A),'YYYY')

When adding a (Vizlib) filter to my sheet with 'Year' as dimension, it shows a whole lot of duplicates:

My goal is to 1) see only one value of each year and 2) limit the amount of years shown (eg. 2019-2021).

I've tackled this last issue in the 'Filter Values' section of the dimension with this conditional:

=IF(Year>='43466' and Year<='44926',1,0)

I just don't understand why it doesn't properly extract the year as a simple value. 01/01/2021 will still be extracted as '44197' instead of '2021'.

Version information

Qlik Sense Enterprise September 2019 Patch 6

qliksenseserver 13.42.12

Vizlib Filter 3.4.0

Thank you for your time!

Timmy

[rodjager · Accepted solution]

Hi Timmy,

Yes, dates can be tricky.  I think the issue here is that Qlik is simply formatting your date field as just a year, however, when you display the values you are seeing a year for each date.

A calendar table may help here but another quick option might be to try something like either:

- Year(Date_A) // Will return the four digit year;  or

- date(yearstart(Date_A) ,'YYYY')  // Will return the date as the first day of the year eg 01/01/2020

Hope this helps.

Rod

[AbhijitBansode]

If date is not recognized properly, then let Qlik know that it's a date using Date# function and then wrap it in Date function to get the date in whatever format required.

You can also just load Left (SOurceColumn,12) while loading so that only date part of timestamp is loaded and then perform above action.

Usually it is not required as floor function takes care of trimming the time part.

Qlikgards,

AB

[TimmyCNHi]

Hi!

Those pointers did help but I think I messed something up on the data end. I had been using only the script but then had to synchronize scripted tables in order to generate calculated fields. Then tried to manipulate the script by unlocking it and soon it all became somewhat of a mess. 😊

I solved this by recreating the app entirely and moving the calculated fields to an Excel that in turn grabs the needed information from an Access database. Like this I can stick with just the script editor and I have more control over the source data. All good now! Thanks!

Cheers

Timmy