1 Reply Latest reply on Oct 28, 2016 2:19 AM by Simon Runc

    How to create rank for latest month

    vitavas itthianuwat

      Hello, Tableau people.


      I came across the issue where I need to create rank for the latest month.

      Right now, I am using index and custom sort to find the solution.

      Is there a better way to do this in tableau without manual sort when the new month happen?

        • 1. Re: How to create rank for latest month
          Simon Runc

          hi Vitavas,


          So one way to do this is to use a blue pill. Tableau will order everything to the right of a blue pill in ascending order of that pill (so the negative of SUM of Sales for the Last Month gives us the descending sort we are after.


          So I created the following calculation

          [Sales Last Month of Data - Sorter]

          {FIXED [Sub-Category]: SUM(IIF(DATETRUNC('month',[Order Date]) =

          {MAX(DATETRUNC('month',[Order Date]))},[Sales]*-1,0))}


          So this only returns sales for the last month of the data, this gets SUMed and then I use the FIXED LoD so we only return a single value per Sub-Category. By then placing this to the left of sub-Category, we get the desired sort. I've left the header unhidden, so you can see what's going on, but can hide in your final solution.


          This is fully dynamic, so when new data comes in it will pick up the last month, and apply the sort.


          Hope that makes sense, and does the trick.