I have two questions and one guess:
Q #1) I'm not understanding the need to take a date field, convert it to a string, and then back into a date? You can use custom formatting on the original date, or if you really need a separate field just duplicate the original and then use the custom formatting on that. If your data source is into the millions or billions of records the calc as written could significantly slow down the view due to the string conversions.
Q #2) When posting questions to the forums it's really helpful for us answerers to have a Tableau packaged workbook (.twbx) with some sample data, or at least a screen shot of your problem. Since Tableau is a highly configurable piece of software there could be a number of reasons why you're not getting the sort.
G #1) I'm guessing that a) the view is using a data blend where the date is coming from the primary and the measure from the secondary or vice versa, or b) the view is using 1 data source and the measure is a table calculation. Tableau doesn't give us the pill sort option for primary dimensions & secondary measures, nor for secondary dimensions, and not for sorting dimensions based on table calculations. In those cases, the usual solution is to set up a 2nd measure that has the right alphanumeric sort (for example if you want to sort based on a number in descending order then you'd use -[original measure]), make it discrete, then put it on Columns to the left of the original dimension pill and then turn off Show Headers for that pill.
First, it appears you are turning a date-type field into a string and then back into a date. What's that all about?
Correct Shawn. Yes, I probably should've mentioned that the reason I am doing that is to prevent the drill-down (+ icon) from appearing next to the dates.
Since I am referencing monthly dates only, if a user clicks the + icon, to drill into the weeks, the sums become distorted and are not correct (as I don't have weekly dates in my reference data). Hence, the reason I am doing this is to hide the + icon.
Just seen your reply Jonathan. Thank you. To confirm, yes I am using a data blend where the date comes from primary and the measure from secondary. I can sort if I have the column pill as MONTH(Date) but not when the column field is my calculated field.
As per my response to Shawn, the reason I went for a calculated field is to prevent drill down into MONTH(Date).
1 of 1 people found this helpful
Matt if you right-drag your date field onto a shelf and select the MY() option you will get the dates without a drill-down option:
You also get a sort option:
Does this work for you?
NOTE: Jive seems to be 'sluggish' this morning. Jonathan's post wasn't there when I posted my answer. Weird.
MY() is something I did not know about - that is really useful and I can see the sorting option now. Thanks for your suggestion Shawn.
However, something which puzzles me is that, despite have relationships on the month and year for the date field in each of the two data sources, in order to get the mark sums to appear correctly, I also need to add YEAR(Date) and MONTH(Date) to the columns shelf and then hide the headers.
I can't understand why this would be necessary and it causes a problem when working with a line graph, because the addition of these fields (with Show Header unticked) causes the lines to disappear between the marks.
I have just worked it out. I need to make the date relationships between the two data sources also use MY()!
Now I am getting the correct sums on the marks without adding the invisible headers to the columns shelf. And now I am not losing the joins between the marks on my other worksheet which represents the data as a line graph.
Thanks to both Shawn and Jonathan for their help!