4 Replies Latest reply on Feb 8, 2016 5:39 AM by Niko Suomi

    Sort By Field when using Empty Columns

    Tom W

      Hey all,

      We report data in rounds which are effectively months and we have some custom labels for those which are formatted like R[xx] - YY

      When faced with a bunch of strings like R01 - 14, R02 - 14, R01 - 15 obviously Tableau will order them in a manner which doesn't reflect the actual time and date and time. To get around this, we use the Sort > By Field and select the ID of the reporting round as it's numerical ordered in the correct sequence. And it works awesome!

       

      product1.PNG

       

      Where it fails, is when we are using show empty columns and we don't have data. So in the above view if we just show seasonal it orders the items with data first then everything else.

      product2.PNG

       

      I'm pretty sure I understand why it's doing this - Because we're sorting by another field and none of those rounds have data, the other field we're sorting by doesn't have any data to lookup in those blank periods.

       

      Our end goal here is to keep the field order like R09 - 14.

      Some solves I've already considered:

      • Move the year infront so it order by that first i.e. 14 - R09. This might be our ultimate fallback but I'd prefer to try keep the year second.
      • Alias the ID field so the sequence stays correct but we can label it whatever we want - not an ideal solution as we would need to maintain it every month across several datasources in one report alone.

       

      Does anyone have any other creative solutions?

        • 1. Re: Sort By Field when using Empty Columns
          Matt Lutton

          Does converting Product Type to a Table Calculation (LOOKUP) filter resolve the problem (not sure how well this will work if you have more dimensions in play, etc.) for you?

           

          I am not 100% certain I interpreted the problem correctly, so I could be way off here.

           

          See the attached.

           

          TCFilter.png

          1 of 1 people found this helpful
          • 2. Re: Sort By Field when using Empty Columns
            Tom W

            Interesting solve Matt, thanks!

            I do have about 4 filtered dimensions in my real example across about 20 sheets using 5 different datasources so it could be a bit of a challenge to implement but I will evaluate later today and check it out.

            1 of 1 people found this helpful
            • 3. Re: Sort By Field when using Empty Columns
              Niko Suomi

              Interesting solution!

               

              I did use this on my own problem that I had with my workbook.

              There were many options on the dimension and I did that lookup for the dimension.

              LOOKUP(MIN([dimension]),0)

               

              After that, I got a #missing -value for the dimension.

              Solution was to filter a filter on quick filters with a duplicate of that dimension.

               

              Drag the original dimension to quick filter and exclude or untap the #missing value:

               

              Drag the duplicate of that dimension to quick filter dimension and click option Use all

               

              Use the duplicate dimension as filter (show filter). You should not see no more #missing on the filter menu.

              • 4. Re: Sort By Field when using Empty Columns
                Niko Suomi

                Hmm.. Actually I am still having the same problem.

                 

                I am chancing the dimension values through parameter, so the lookup is chancing all the time.

                And it does not sort the month\year value accordingly