3 Replies Latest reply on Nov 29, 2018 12:49 PM by Joe Oppelt

    Sorting by Latest Date?

    Alejandro Gutierrez

      Hello,

       

      Attached is a workbook with an example of the issue. The goal is to automate the sorting by the latest date. Think you can help me out?

       

      We want to keep the row grand totals.

       

      This post is as close as I've gotten. I wrote the following calculation, but it does not come up as a sort option:

      IF ATTR( DATEDIFF('day',[Order Date],TODAY())) = 0

      THEN [Profit Ratio]

      ELSE NULL

      END

        • 1. Re: Sorting by Latest Date?
          Joe Oppelt

          Several things.

           

          Will your last date always include TODAY()?  What if your data is a day old?

           

          If you'll always have TODAY, then you can rely on that to find the last date.  Otherwise you'll want to have some LOD calc to tell you the last date on the sheet.


          this is what that LOD would look like for that sheet:

           

          { EXCLUDE [Order Date],[Segment] : MAX([Order Date]) }

           

          (If you have more dimensions on the sheet, add them in the EXCLUDE list.)

           

          In the attached I have done that, and I changed the filter to select values rather than be a relative filter to show you how it works.  And I displayed the value in the title.

           

          If you want to do that, then you'd compare against the LOD rather than TODAY().  (And you don't have to do the datediff.  You can just say,

           

          IF attr([Order Date]) = ATTR([Latest Date])  then...

           

          Next thing:

           

          You want to sort by an aggregate calc.  That's what your calc is doing.  It's taking data only for the target date.  That's what you want to sort on.  Add it to your sheet on the data shelf first.  Make it DISCRETE.  Then you can drag it in front of [Segment] on rows, and that's how you get Tableau to sort on an aggregate like that.

           

          Note:  Tableau will only sort ascending for this.  If you want descending, then in your sorting aggregate, multiply [Profit Ratio] by negative-1, and that will effectively create a descending sort.

           

          Once you have the sort doing what you want, uncheck "Show Header" for the pill on ROWS.

           

          BUT!!!  That precludes you from doing a Grand Total.  I think you might have discovered that already.

           

          To address that, create a separate sheet that makes your own grand total using WINDOW_SUM(SUM([Profit])) / WINDOW_SUM(SUM([Sales]))

           

          Position that on a dashboard at the end of your actual data sheet, and the user won't know the difference.

           

          See Sheet 1(2) in the attached.  I have that calc on the sheet.  Notice that it displays on all dates.  Go to Sheet 1(3).

           

          My [index] calc is just the index() in the table.  I put that on filters, selected for value = 1, and now I get only one mark on the sheet.  I hid the headers for all the dimensions so that it will nestle nicely on the dashboard next to your actual data sheet.  To get "Grand total" in there I double-clicked in the columns shelf and just typed that in.  You could have made a calc to do the same thing, but this is quick-and-dirty.  I also hid the label for that (but not the header).


          And you can see it positioned on Dashboard 1.

           

          I didn't add the sort field to this.  You can take care of that.  And you will want to make the date filter "apply to" both sheets so that you get the same results on both sheets.  (I didn't do that either.)

          • 2. Re: Sorting by Latest Date?
            Alejandro Gutierrez

            Wow Joe, awesome thorough response.

             

            Your dual sheet solution for the way I asked my question. Unfortunately, our production workbook has more than 3 rows, it can have upwards of 50 rows, so we end up with two synchronized vertical scroll bars.

             

            I do like your LOD latest date approach. Our data is sometimes more than a day old.

            • 3. Re: Sorting by Latest Date?
              Joe Oppelt

              Alejandro Gutierrez wrote:

               

              ...Unfortunately, our production workbook has more than 3 rows, it can have upwards of 50 rows, so we end up with two synchronized vertical scroll bars.

               

              I do like your LOD latest date approach. Our data is sometimes more than a day old.

              Yup, that can be a problem.  But the only way to get a sort on an aggregate like you need there is to put it in the front of the rows pills, and that precludes getting Tableau generated grand totals on that sheet.

               

              I've chatted with dashboard developers about the concept of a scrolling container (which would thereby scroll both sheets simultaneously when you scroll the container).  The considered the concept interesting.  And there are threads in the IDEAS forum for this.  You can vote them up.  Here is one:

               

              https://community.tableau.com/ideas/3055