Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Tell us which business and trade publications you read most regularly: RESPOND NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
pankaj999
Contributor III
Contributor III

Convert long integered number to date in qliksense set analysis

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.

1.JPG

FYI my data is in below UNIX format 

2.JPG

1 Solution

Accepted Solutions
pradosh_thakur
Master II
Master II

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')
Learning never stops.

View solution in original post

4 Replies
Channa
Specialist III
Specialist III

not sure

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

 

check with ur data

Channa
pankaj999
Contributor III
Contributor III
Author

@Channa

No it gives 00:00:00 for all.
pradosh_thakur
Master II
Master II

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')
Learning never stops.
pradosh_thakur
Master II
Master II

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

Learning never stops.