Take a look at the attached, I used a parameter and a calculated field to allow the user to change the sort order of the first dimension.
Would you like any details on any of the techniques used in this workbook?
Select Sort.twbx 68.5 KB
Almost there apart from getting the Parameter to show as a Quick Filter, think I have everything else working
Show Parameter control
Brilliant.....Thanks Joe all working
One last question on this
Certain Columns I would want in Ascending order (Last Name)and others in Decending order (Date=Newest) can this be done?
It seems so, I dont know what I have done but Name is sorting A-Z and rating is sorting Z-A??????
I don't know of an easy way to control that without preprocessing.
Thinking about, likely over-thinking, maybe an interesting/complex set of parameters and table calculations could allow you that control.
Anyone know if there is an easier way?
My first thought (which I'm sure Joe had already discarded) was that a table calculation which just swaps the order of rows on the fields you want as descending sorts would do it. But you can't sort on the results of a table calculation (which makes sense - everything would get very circular). You could still do something by defining calculated fields to reverse the order of all fields, but that would be just horrible.
The best I can see is that for any fields that you want to sort descending you'd need to create an expression which returns something which sorts in the other order and use that instead of just the original field name in the CASE statement in the [Sort] calculated field. That's slightly tricky because it needs to be a string expression, so numeric fields would need to be padded with zeroes, for example. (A FORMAT() function would be really handy here.) So for your date field you want to create a calculated field saying how many days ago the date was which will give you newest first. So:
NOW() - [DATE]
Would give you the number of days since the date, which will sort the way you want numerically. But to turn that into a string so that "2" comes before "11" you need to pad it. If you need to cope with future dates you also have to cope with that expression going negative, so the easiest would be to add a huge offset to the number. This is good for the next few hundred years:
RIGHT("000000" +STR(TODAY()-[Date]+100000), 6)
So assuming there was an additional [Date] field in the example Joe posted, the [Sort] calculated field would become:
CASE [Sort by]
WHEN "Customer Name" THEN [Customer Name]
WHEN "Region" THEN [Region]
WHEN "Zip Code" THEN STR([Zip Code])
WHEN "Date" THEN RIGHT("000000" +STR(TODAY()-[Date]+100000), 6)
ELSE NULL END
Great idea Richard, I was over thinking it :)
but why not just:
WHEN "Date" THEN -FLOAT([Date])
for the line in question.
EDIT: Does not work because it is not text -I'll have to come back to this in a bit
The issue I see with using TODAY/NOW functions is what if the date is in the future?
The future date issue is why I added 100000 to the number of days (which is 274 years, so I won't be around to care when that stops working). That just avoids the problem with sorting on text representations of negative numbers. A bit grubby, but it works and I really didn't think it was worth sweating it much more than that.
It almost works :)
I now see the dates in the order of:
would those full dates be:
November 9, 2010
November 8, 2010
November 7, 2010
November 25, 2009
November 24, 2009
November 23, 2009
November 10, 2009
Attached is a workbook showing this sorting like this.
Would you rather it ignore the year, and just sort by the day of year? (also included in attached)
Select Sort edit.twbx 127.4 KB
Sorry all the dates are 2010
I must be missing something because I don't get the same reults as your example.
1. Amended the CASE statement
2. Set the Date dimension to "All values" and "Discrete"
3. If I look at the Calculated field dialogue select the date field in question and "Describe" then "Load" I see a list of dates in dd/mm/yyyy format
Yet I still see the dates sorting as
I dont want to ignore the year btw
Actually I'm lying
I now see the dates as
Then same for Oct followed by Nov
I am unable to find a way to get the sort order your are getting.
Can you provide a packaged workbook showing that sorting situation?
I haven't looked at the workbook or read this carefully - but it sounds a bit like a nasty issue you can get with text data sources (which use the MS Jet engine) - which can mangle dates. I think this is locale dependent. It certainly happens to me in NZ where we don't use US date order.
What can happen is that day and month get transposed but only for the dates where the day <= 12. So 23/11/2010 is unambiguously 23rd of November, whereas 03/11/2010 may get treated as 11th March.
So if your data source is a text file and the original dates are in dd/mm/yyyy order I'd have a careful look at those dates - look at the details of a few rows in the text file and in Tableau and see if the dates are being handled right.
If it is that your options are to reformat your text file, define a schema.ini file as described in a knowledgebase article or do some ugly calculated fields in Tableau to unpick the mess.
Edit: I think that issue I was talking about may happen when you have a text file that doesn't match the default date order for your locale. Maybe. I know we only see it from time to time. I had it this week and that was certainly a file in MM/DD/YY format, which doesn't match the default for us.