2 Replies Latest reply on Jan 16, 2019 11:23 AM by Daniel Lawrence

    LODs, Ranks, and Sorts, Oh My!

    Daniel Lawrence

      Hello all,


        This apparently simple problem has been vexing me today.

      I have attached a sample of the data I am using, anonymized. 


      My goals:

      1. I would like to get the average revenue per hour for the top 10 employees (by average revenue per hour) but only for the previous week along with the YTD average revenue per hour. 
      2. I would like them to be sorted in Descending order based on the previous week's value, not the YTD value.
      3. I would also like their rank visually enumerated (highest = 1, lowest = 10, etc...)


      These all seem like very simple things but these are the problems I have encountered:

      • I have been using the built in Row Totals to compute the YTD and just using a Date Window (lookup(max[date], 0)) to display the Previous Week.  INDEX() or Rank_Unique does not want to work with the built in Row Totals so they do not coexist, either one or the other.
      • My data is in JSON format so any sort of data source duplicating/manipulating tricks are difficult or impossible. 
      • The Sort by field does not look to the previous week's values to determine sort order.  I have yet to figure out which values it is actually looking at. 
      • I have tried to compute specifically the previous week's average revenue per hour using LODs and very easily accomplished the previous week's revenue as a standalone value.
      • The previous week's hours, however, are more difficult.  What I am calling an hour is a distinct time during one day.  (E.g. 12:00 on monday and 12:00 on tuesday are two different hours).  I previously have used {FIXED [Anon Employee], [Date]: CountD([Time] } with success but it only achieves the hours based on the time determined by the dimension.  I want to be able to use this value in a calculation with which to Sort By so it cannot be determined by a dimension.


      Any and all insight would be greatly appreciated!


      Thank you,


        • 1. Re: LODs, Ranks, and Sorts, Oh My!
          Daniel Lawrence

          I forgot to mention but this needs to be dynamic so that in the future it knows to look for the previous week and not just trying to find the current previous week as a static value.

          • 2. Re: LODs, Ranks, and Sorts, Oh My!
            Daniel Lawrence

            It turns out that the built in Row Totals was the cause of all of my problems!  Thanks Tableau. 


            To solve all of my problems:

            • I turned off the built in Row Totals calculation
            • Built a simple LOD for the year to date Revenue/Hour - {FIXED [Anon Emlployee], YEAR([Date]): SUM([Revenue])/SUM([# Hours]) } which used my previous LOD, [# Hours}, to fine the distinct time periods in each day, and used that as a measure value along with basic Average Revenue Per Hour calculated field that would apply to the Previous week values.
            • This makes sorting the top 10 via the sort field in Anon Employee simple as it now understands that the previous week's values (the only showing up in the viz) are the values to be sorted on.
            • And Rank_Unique() also now works because Row Totals is gone.


            Its so nice when addition by subtraction occurs.