Master Calendar question

[#1]

Hello!

I would like to use the typical Master Calendar coding created by the autogenerated coding, that looks as follow:

[autoCalendar]:

   DECLARE FIELD DEFINITION Tagged ('$date')

FIELDS

   Dual(Year($1), YearStart($1)) AS [Year] Tagged ('$axis', '$year'),

   Dual('Q'&Num(Ceil(Num(Month($1))/3)),Num(Ceil(NUM(Month($1))/3),00)) AS [Quarter] Tagged ('$quarter'),

   Dual(Year($1)&'-Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [YearQuarter] Tagged ('$axis', '$yearquarter'),

   Month($1) AS [Month] Tagged ('$month'),

   Dual(Year($1)&'-'&Month($1), monthstart($1)) AS [YearMonth] Tagged ('$axis', '$yearmonth'),

   Dual('W'&Num(Week($1),00), Num(Week($1),00)) AS [Week] Tagged ('$weeknumber'),

   Date(Floor($1)) AS [Date] Tagged ('$date');

DERIVE FIELDS FROM FIELDS [TRANDATE], [OESHDT.AUDTDATE], [ORDDATE], [SHIPDATE], [ARCUS.AUDTDATE], [DATELASTMN], [DATESTART], [DATELASTST], [DATEINVCHI], [DATEBALHI], [DATELASTAC], [DATELASTIV], [DATELASTPA], [DATELASTAD], [DATELASTRI], [AUDTDATE], [ARGRO.DATELASTMN], [ICITEM.AUDTDATE], [ICITEM.DATELASTMN], [DATEINACTV], [ICCATG.AUDTDATE], [ICCATG.DATELASTMN], [ICLOC.AUDTDATE], [ICLOC.DATELASTMN], [ARSAP.AUDTDATE], [ARSAP.DATELASTMN] USING [autoCalendar] ;

__________________________________________

In my database, all dates are formatted this way: YYYYMMDD for example: 20160619. But in Qlik Sense it ends up looking like this '11/01/59876'.

My Set Date format is:  SET DateFormat='DD/MM/YYYY';

So please, what is missing in my AutoCalendar section?

Thanks for your help & suggestions!

[swuehl]

Try

SET DateFormat='YYYYMMDD';

Or use your original default format and interpret all your input fields containing dates using Date#(FIELD,'YYYYMMDD')

Why don’t my dates work?

Get the Dates Right

If your date field values were interpreted correctly as dates, then you can format your values using Date() function (e.g. in the master calendar code section).

[#3]

Qlik Sence stores each date, time and time stamp found in data as a datetime Serial number.

The date time for 01/01/2014 06:00:00 is represented as 41640.25 where:

- The integer part, 41640, represents the date and is the number of days elapsed since Dec.30, 1899.

- The decimal part represents the time stored as a value between 0 and 1. This nummber is the faction of elapsed time from a full day. Therefor, 06:00:00 is six hours elapsed from a full day, or 0,25. The value 0.0 corresponds to 00:00:00, 0.99999 coresponds to 23:59:59.

Example:

Value: 41025

Format: 'M/D/YYYY'

Text displayed: 4/26/2012

When you load data into Qlik Sence, fields containing numbers with a defined data type in a database are handled according to their respective formats.

You can use to define input and Output Formats of Dates:

- Data(), Time() --> Formating functions define the Format for how to present the data in Qlik Sence

- Data#(), Time#() -->   Number Interpretation function that define the data Format of the date being loaded into Qlik Sence.

BR

[brijeshvma]

Hi,

In Your Script u can do like this..

Date(Date#(DateField,'ÝYYYMMDD'')) as DateField.

So it will inform to the qlik that its a date field in YYYYMMDD Field ..

Hope this Helps