I had a bit more of a look at that issue I get with reversed dates because I wasn't quite sure I understood the exact circumstances. I just knew that sometimes reversed dates get mangled so I fix up the order in the text file and all is well.
Turns out there's slightly more to it than I realised.
Jet generally seems to do a pretty good job of guessing the date format. It inspects the first N rows of the file (where N is quite small by default but can be extended as explained in a knowledgebase article) to work out what data types to use for everything.
If the date is in a format which Jet interprets as a date and there are dates where the day is > 12 within the first N rows then it correctly works out which are the days and which are the months, independent of the order of the parts and the locale settings.
If the date is in a format which Jet recognises, but the first N rows don't have any examples where day > 12, it seems to assume the date order based on locale - which means it can get it wrong. In that case, any dates where day > 12 get set to NULL (because Jet is trying to treat day as month.
The case where it goes most wrong is if the format doesn't get treated as a date by default, so the field defaults to string, but can be turned into a date by just selecting Change Data Type from the field's context menu. In that case, the conversion seems to be done on a row by row basis. The rows with day <= 12 get treated one way and the rows with day > 12 get treated the other (correct) way.
The case I hit this week had data for 4 days: 10th, 11th, 22nd and 23rd November, with the dates in US format, and the first 50,000 rows being for 10th and 11th, so ambiguous. The fields were in this format: "11/11/10 11:55 PM". These were initially treated as strings, but after changing the data type to datetime in Tableau, they were shown as 11th October, 11th, 22nd and 23rd November.
I can't think of any smarter ways of handling this, by the way - but it's definitely a nasty little trap to watch out for.
After all that, I'm not sure if it explains your weird sort order anyway. If it doesn't, post a sample workbook.
Does anyone know how to dynamically change data sort order on a dimension that is invoked by another calculated field? I cannot find any functions e.g. DESC(), ASC() that would allow me to do it on the fly. Cheers, David
webcoder, yes you can setup your sheet in a way that allows for a user to select from a parameter control how to sort.
see The specified item was not found. for a slightly advanced implementation, basically you add a negative sign to reverse the order, and you can turn text/strings into a number with a function like INDEX()
This is a great idea for a tutorial video, thanks.