This might have something to do with how your datasource is set up to display dates.
If you right click on your datasource, select the Date Properties menu item a window pops up in which you can specify week start, fiscal year start and date format. It'll probably be set to automatic but you can change it to display dd/mm/yyyy like so:
If you use this method I think using DATEPARSE() function will run quicker than the calculation in the image you attached.
Hope that helps, let me know if it doesn't work for you
I have followed the steps mentioned above but it has made no difference to field; it is still displaying as it did in my first image.
I haven't used the DATEPARSE() as speed isn't an issue at the moment only getting the data correct is.
Any other ideas on what I'm doing wrong.
You can also edit the date format by right clicking on the date field and selecting default properties > date properties and if you choose the option of 14/03/2001 that can override the date properties setting at datasource level.
Another way would be to right click on the date in a view and select Format and change the Dates section to 14/03/2001
I apologise if you already knew about those options, I'm just trying to figure out what might be causing the date to appear wrong because when I've tried to replicate the error (using some dummy data in excel) I can't get the same error as you do.
If neither of those things work, are you able to share your workbook (save as a .twbx) on this thread? That'll make it much easier to find a solution (obviously remove anything customer sensitive, you could just take the date fields and paste into excel then connect to that and see if you can replicate the error)
Well, I have tried everything suggested and still no joy.
I have tried to change the format at the DS and in the view with no luck.
But the plot thickens.
I exported a small subset of the data as a CSV than added it as a new data source and both the fields that are string when connected to the live data are automatically detected as Date fields, exactly as I need them. So I'm lost as to what the issue is.
Unfortunately exporting the data is not a suitable solution as I need to be able to refresh these reports from the source weekly (preferable from the Tableau server) along with about 10 other large dashboards. The prod data source has about 3 million rows so converting to CSV just doesn't work.
Are there any other options e.g convert the data in the SQL query as changing the DB is too big of task as regression testing would be cumbersome.
1 of 1 people found this helpful
If your data connection is Custom SQL then I'd definitely try to convert the date text field to a date field in the query, something like SELECT convert(datetime, <Your Date String>, 103) might work, depending on your database (obviously Oracle would be different).
You could even try, in the datasource window, changing the data type of your date field from string to date by clicking on the "Abc" menu highlighted here:
Really hope we can work out a solution, sounds like a really frustrating issue!
I modified the query this morning and all seems to be working.
Adding the "convert" worked but when I added a WHERE on that field I received errors due to dodgy data but one of the more technical guys here fixed the issue with
WHERE ISDATE([vwComplianceAssessment].[CA_DATE]) = 1 AND CONVERT(datetime, [vwComplianceAssessment].[CA_DATE]) >= '2016-01-01'
Thanks again for the input.
Brilliant, glad you found a solution.