Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
hucand
Contributor II
Contributor II

Timestamps not recognized as dates

I've got a field called Timestamp - they're all formatted like:

12/19/2018 11:53:57 AM

When I load with:

SET TimeFormat='h:mm:ss TT';
SET DateFormat='M/D/YYYY';
SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

LOAD

    Date(Timestamp("Timestamp")) as Date

My dates are formatted correctly - the example above displays as 12/19/2018.

How do I set this as a date field so that the options for Month, Year, YearMonth, etc show in the sheet editor?

hucand_0-1659040739015.png

 

Labels (3)
2 Solutions

Accepted Solutions
Channa
Specialist III
Specialist III

if you are not using script editor it will , you just add file and use association model

 

or if you use script editor check for master calendar this is some thing you need to create

Channa

View solution in original post

lucpechali
Partner - Contributor III
Partner - Contributor III

Hi,

You should know that when you format a timestamp field as a date field Ex: date(timestamp) Qlik remembers the time part of the field. If you subsequently make equality on the date field, this may generate errors or not make equality.
It is therefore necessary to remove the hours from the timestamp with :

makedate(year(timestamp),month(timestamp),day(timestamp)) as Date

You will be sure to have only one field that only contains dates.
And in this case, they will be correctly formatted in measures.

View solution in original post

3 Replies
Channa
Specialist III
Specialist III

if you are not using script editor it will , you just add file and use association model

 

or if you use script editor check for master calendar this is some thing you need to create

Channa
lucpechali
Partner - Contributor III
Partner - Contributor III

Hi,

You should know that when you format a timestamp field as a date field Ex: date(timestamp) Qlik remembers the time part of the field. If you subsequently make equality on the date field, this may generate errors or not make equality.
It is therefore necessary to remove the hours from the timestamp with :

makedate(year(timestamp),month(timestamp),day(timestamp)) as Date

You will be sure to have only one field that only contains dates.
And in this case, they will be correctly formatted in measures.

hucand
Contributor II
Contributor II
Author

I ended up using this from an autogenerated script, not sure if that's what a master calendar is -

[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', '$cyclic'),
Dual(Year($1)&'-Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [YearQuarter] Tagged ('$yearquarter', '$qualified'),
Dual('Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [_YearQuarter] Tagged ('$yearquarter', '$hidden', '$simplified'),
Month($1) AS [Month] Tagged ('$month', '$cyclic'),
Dual(Year($1)&'-'&Month($1), monthstart($1)) AS [YearMonth] Tagged ('$axis', '$yearmonth', '$qualified'),
Dual(Month($1), monthstart($1)) AS [_YearMonth] Tagged ('$axis', '$yearmonth', '$simplified', '$hidden'),
Dual('W'&Num(Week($1),00), Num(Week($1),00)) AS [Week] Tagged ('$weeknumber', '$cyclic'),
Date(Floor($1)) AS [Date] Tagged ('$axis', '$date', '$qualified'),
Date(Floor($1), 'D') AS [_Date] Tagged ('$axis', '$date', '$hidden', '$simplified'),
If (DayNumberOfYear($1) <= DayNumberOfYear(Today()), 1, 0) AS [InYTD] ,
Year(Today())-Year($1) AS [YearsAgo] ,
If (DayNumberOfQuarter($1) <= DayNumberOfQuarter(Today()),1,0) AS [InQTD] ,
4*Year(Today())+Ceil(Month(Today())/3)-4*Year($1)-Ceil(Month($1)/3) AS [QuartersAgo] ,
Ceil(Month(Today())/3)-Ceil(Month($1)/3) AS [QuarterRelNo] ,
If(Day($1)<=Day(Today()),1,0) AS [InMTD] ,
12*Year(Today())+Month(Today())-12*Year($1)-Month($1) AS [MonthsAgo] ,
Month(Today())-Month($1) AS [MonthRelNo] ,
If(WeekDay($1)<=WeekDay(Today()),1,0) AS [InWTD] ,
(WeekStart(Today())-WeekStart($1))/7 AS [WeeksAgo] ,
Week(Today())-Week($1) AS [WeekRelNo] ;

DERIVE FIELDS FROM FIELDS censored;