2 Replies Latest reply on May 9, 2018 7:17 AM by Fabio Fantoni

    Average of Ranking based on Average

    Fabio Fantoni

      For a certain data range, for a specific dimension, I need to calculate the average value of a daily rank based on the average value.

      First of all this is the starting point:

      2018-05-07 11_02_57-Tableau - Book1.png

      This is quite simple and for each day and category I get the AVG(value) and the Ranke based on that AVG(Value) computed using Category.

      Now what I need is "just" a table with one row for each Category with the average value of that rank for the overall period.

      Something like this:

      2018-05-07 11_24_10-aggregate functions - Tableau - Average of Ranking based on Average - Stack Over.png

      I tried using the LOD but it's not possble using table calculations inside LOD so I'm wondering if I'm missing anything or if it's even possible in Tableau (but I'm pretty sure it is )

      You can find the workbook with the raw data at the following URL or attached
      Tableau Public

       

      Thanks in Advance

        • 1. Re: Average of Ranking based on Average
          Michel Caissie

          Fabio,

           

          You can get those numbers using table calculations.

          If you check on the sheet validateData

          1- I get the  sum of the rank averages with

          WINDOW_SUM( [Value (Rank)] )

          where Value(Rank) is

          RANK(AVG([Value]))

           

          this is a nested calculation, so you need to set a specific computing on each

          Value(Rank)  computes using  Category  and

          Value (Rank winSum) computes using  Category, Day of Date  Restarting every Category

           

          2- I get the number of Days with

          WINDOW_SUM( COUNTD( DATETRUNC('day', [Date]) ) )

          computing on Day of Date

           

          3-So you get  Value (Rank Avg) with

          [Value (Rank  winSum)] / [Day (Number of)]

           

          4-And to  show a single value per Category, I build a filter  'keep single date' with

          if index() = 1 then 1 else 0 end

          computing on Day of Date

           

          The finalView sheet is a duplicate of the validateData sheet  where I

          1-move keep single date  to the filter shelf and keep the  1s

          2-keep only the Value (Rank Avg)  measure

          3-Hide the Day(Date)  Header

           

          Michel

          • 2. Re: Average of Ranking based on Average
            Fabio Fantoni

            Thanks a lot Michel!

             

            Really useful