In your Data Source (the Extract file), what is the name of the field that contains the Unix timestamp values? Does Tableau see that field as an Integer ? If so, you merely need to use your field name in the Calculated Field instead of the one used in the example.
When creating or editing a Calculated Field, anything enclosed in square braces must be the name of a data field that exists in your Data Source.
Thx Ken. The field is converted to a string from Hubspot and the TDE was created with Progress Easyl. Let me try your suggestion as I clearly misunderstood the calculation syntax.
The formula given already uses INT to convert a string to an integer, so you should be all set so long as you substitute in the correct field name. Let us know if you have any trouble.
Here's the string: 1392336067000
According to Epoch Converter - Unix Timestamp Converter, this is 4/16/15
When I enter the calculation below, I get a year 46091
DATE(DATEADD('second', INT([RECENT_DEAL_CLOSE_DATE]), #1970-01-01#))
I tried to truncate it to 10 digits as follows, but get the year 2014.
DATE(DATEADD('second', INT(LEFT([RECENT_DEAL_CLOSE_DATE],10)), #1970-01-01#))
Today is 4/16/15 and the UNIX timestamp for that is closer to the range of 1429193933 according to that same Epoch converter website.
When I copy-paste your value of RECENT_DEAL_CLOSE_DATE into that same website, it returns Feb 14, 2014 with the assumption that the value was in miliseconds.
To that end, I suggest dividing your field by 1000 and the calculation appears correct to me.
Please see screenshot and workbook attached.
I misread the epoch converter. The DIV 1000 is a great idea and this is working:
DATE(DATEADD('second', DIV(INT([RECENT_DEAL_CLOSE_DATE]),1000), #1970-01-01#))
Thanks again for helping out a newbie.
You're welcome. Just please remember to mark your thread as Answered.
FYI, the outermost DATE() call in your calculation is unnecessary. DATEADD() returns a date. You can also simply use the "/" operator to divide by 1000.
dateadd('second', int(RECENT_DEAL_CLOSE_DATE / 1000), #1970-01-01#)
You are right that the outer DATE is unnecessary but it was in the original document / example that Sumit was following.
If you look at the .twbx I attached to my post you'll see I used /1000 in my calc.
FYI, the outermost DATE() call in your calculation is unnecessary. DATEADD() returns a date.
It is actually necessary if one wants the data type to be DATE. Otherwise the calculation data type will be set DATETIME which might include time hh:nn:ss. It is seemingly not possible to set data type of calculations manually and therefore necessary to do this in the calculation itself.