8 Replies Latest reply on Mar 31, 2016 6:49 AM by Matt Botten

    Sort option unavailable on calculated date column

    Matt Botten

      I have created a custom date field using this formula:

      DATE(STR(YEAR([Date])) + "/" + STR(MONTH([Date])) + "/" +  STR(DAY([Date])))

       

      This has then been formatted as mmm yy.

       

      I've then dragged it to the Columns shelf as a discrete value. I am then summing up a measure on the marks shelf.

       

      When I click the date column header, the sort option is not available. Exclude/Include etc. are available but no sort option appears.

       

      Any ideas?

        • 1. Re: Sort option unavailable on calculated date column
          Jonathan Drummey

          Hi Matt,

           

          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.

           

          Jonathan

          • 2. Re: Sort option unavailable on calculated date column
            Shawn Wallwork

            First, it appears you are turning a date-type field into a string and then back into a date. What's that all about?

             

            --Shawn

            • 3. Re: Sort option unavailable on calculated date column
              Matt Botten

              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.

              • 4. Re: Sort option unavailable on calculated date column
                Matt Botten

                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).

                • 5. Re: Sort option unavailable on calculated date column
                  Shawn Wallwork

                  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?

                   

                  --Shawn

                   

                  NOTE: Jive seems to be 'sluggish' this morning. Jonathan's post wasn't there when I posted my answer. Weird.

                  1 of 1 people found this helpful
                  • 6. Re: Sort option unavailable on calculated date column
                    Matt Botten

                    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.

                    • 7. Re: Sort option unavailable on calculated date column
                      Matt Botten

                      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!