Qlik Sense: Can't create calendar measures

[cb9797]

I'm trying to cast a Unix timestamp as a readable date for QS. I need it to be a date to create a filter pane with calendar measures such as YTD, QTR, etc. I have two tables with the same date format, uploaded to the same app, and need to this for both of them.

I've tried to achieve this by creating a new calculated field through data manager with the following function:

- date(floor(<timestamp> /86400+25569 ))

I've also tried:

-date#( date(floor(<timestamp> /86400 +25569) ),'MM/D/YYYY' )

Both give out the correct date format ("10/1/2020") but QS still doesn't recognise the columns as a date and I still can't create calendar measures. The first function has worked for me in the past but not now.

The tables I'm using where both uploaded from data manager from a Hive database.

This is what I want but "Fecha" only appears as a regular column:

[edwin]

Qlik Sense automatically does hat for you if you use the data manager.  however, you can still imitate the script by coding it yourself:

load
date(floor(1611518385  /86400+25569 )) as D
autogenerate (1);

[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 [D] USING [autoCalendar] ;

the autogenerate part is standard, you just have to specify the field [D] in this sample in the last statement.  to test create a table and in the dimension browse for your date field ([D])

[edwin]

[cb9797]

That's what had happened before but not now. What do you think could be the issue? Ok thanks. I'll try to do that. Should I try that code on Data Load Editor? Or as a new calculated field?

[edwin]

copy paste in load editor (after the load for your date field)

[edwin]

to be clear copy starting from the autocalendar, the load before that in my script was just to simulate your date field since you didnt supply your load script

[cb9797]

I downloaded the data with data manager so there was no code. With Load editor it should look something like this right? Where should I copy the code?

LIB CONNECT TO 'HIVE_CN';

LOAD 'COL1',

            'COL2',

            'UNIX_TIME';

SQL SELECT 'COL1',

                          'COL2',

                           'UNIX_TIME'

FROM HIVE.DB.TBL;

[edwin]

but this isnt what you used to build the date field

date(floor(<timestamp> /86400+25569 ))

where im assuming you will replace <timestamp> with the field UNIXTIME

after yiou build teh date field, add the code i pasted

[edwin]

another way, add the expression in data manager where you build the date field, the autocalendar will be generated by QS

[cb9797]

I tried to do it like that and it worked! Thanks a lot for your help. One last question. When I tried to rerun the script it threw an error that said something like "Duplicated Derived field". How do I fix this error when I rerun the script with updated Hive tables?