Is the column in Excel formatted as a date, to only show the Year value? (eg Customer Format='yyyy')
It might be that the underlying data is a full date, but you're only seeing the year part of it in Excel. Tableau sees the full version of the data.
If that's not it, can you post an excel file with a couple of columns in it, including the date column that's giving you problems?
Excel (and Tableau) treat dates as a number of days since 30th December 1899 (long story, it was meant to be 1st January 1900, but long ago back in the days of Lotus 1-2-3 someone got it wrong and it stuck).
Anyway, day 2,012 on that counting scheme works out to be 5th July 1905.
So your year column is just being treated as the number of days since the start of the Excel and Tableau view of time.
Easiest thing is just to leave the data type of your [year] field as a number and then create a calculated field [year as date] (say) like this:
DATEADD('year', ([year] - 2000), #January 1, 2000#)
Thanks, Richard. That makes sense, though I couldn't get your formula to work ["All fields must be aggregate or constant when using table calculation functions or fields from multiple data sources."] In addition, I seem to have the same problem even when reading in the data as a text file. I've copied the data below.
Is this an inherent issue in Tableau? Is it the case that whenever a data set comes in with a year variable and the user tries to convert it to a date, Tableau automatically converts it to the Dec 30, 1899 index?
Anyways, any other suggestions are very much appreciated.
fsize year grossup grossdown net 1 1990 2 11 1 2 1990 8 20 13 3 1990 1 2 3 4 1990 10 15 20 1 1991 4 14 5 2 1991 8 20 13 3 1991 1 2 3 4 1991 10 15 20 1 1992 6 17 9 2 1992 8 20 13 3 1992 1 2 3 4 1992 10 15 20 1 1993 8 20 13 2 1993 8 20 13 3 1993 1 2 3 4 1993 10 15 20 1 1994 10 23 17 2 1994 8 20 13 3 1994 1 2 3 4 1994 10 15 20
Can you attach the actual CSV and TWB files you're seeing these problems with? I have never come across this as a problem.
It might be a problem for support if we can't get much further on this forum!
I'm not sure what's going on on your machine! I just connected to the Text file, converted Year from a Measure to a Dimension and was good to go (attached)
I think you might have to email support! Sorry I couldn't help you
Book3.twb.zip 3.6 KB
I think Jonathan was wanting it to be treated as a date datatype, Andy, rather than just a number.
Attached version of Andy's workbook has that calculation I suggested in it. I can't quite think why you would have been getting that error message.
year.twbx.zip 4.2 KB
When you import a field from Excel that contains only the year value(YYYY), Tableau recognizes the field as an integer rather than as a date. The date format recognized by Tableau is DD/MM/YYYY.
follow the following steps to resolve this problem
Select Analysis > Create Calculated Field.
- In the Calculated Field dialog box that opens, type a name for the field.
- In the Formula box, type the following formula:
DATE("01/01/" + STR([Year]))and then click OK.
- Use this new calculated field in the view instead of the date field.
let me know if this solves your issue:)