Convert long integered number to date in qliksense set analysis

[pankaj999]

Hi,

How to convert this to date in set analysis ?

"1550763052218"

I got this number as date from hubspot's api .

On trying  below to get the latest date

date(Max(value),'MM-DD-YYYY hh:mm:ss')

I get the following response, the year and time stamp is not correct, rest looks fine.

FYI my data is in below UNIX format 

[Channa]

not sure

time(date(1550763052218),'YYYY-MM-DD hh:mm:ss')

check with ur data

[pankaj999]

@Channa

No it gives 00:00:00 for all.

[pradosh_thakur · Accepted solution]

Try the below

ConvertToLocalTime(timestamp((25569+(1427997766000+3600000)/(1000*24*3600))), 'GMT-01:00')

or

ConvertToLocalTime(timestamp((25569+(your_hubspot_api_date_field+3600000)/(1000*24*3600))), 'GMT-01:00')

[pradosh_thakur]

this is referred from the below link

https://community.qlik.com/t5/New-to-QlikView/Convert-unix-time-stamp-to-date-and-time/td-p/384453

Remember to change it to your local time gmt+-xx:xx hours to get the exact time