1 Reply Latest reply on May 10, 2017 4:47 AM by Prayson Wilfred Daniel

    Table Rank Calculation across entire period and not by day (Granularity of the View)

    Thomas Karbe

      I have attached a Workbook to better illustrate what I am trying to do.


      My first step was to take the deviation of the weekly average (Sales) from the monthly average (Sales) per product. This is a Table Calculation calculated for every Created At Day. So for every day we have the deviation of the week from the monthly average. 


      Screen Shot 2017-05-10 at 11.45.00.png

      However, I would only like the show the Top 3 Products and not all 5 of them. Top is defined as the products with the largest average absolute deviation from 0. I calculated the average absolute deviation from 0 with another Table Calculation and I received this result.


      Screen Shot 2017-05-10 at 11.47.42.png

      Here you can see the average absolute deviation from 0. The value is the same for everyday of course. I am actually only interested in the value and am currently only showing by day, because I needed Created AT Days in the view to calculate the Table Calculation above.

      I would somehow like to take the top 3 Products from this graph and then apply it as a products filter. I am not completely sure how to do this. I have tried creating a set with top N, but I cannot use the Table Calculated field to rank this. Now I am trying to create a Rank Function,

      but am unaware how I can get a simple RANK (1,2,3,4,5) when the (average absolute deviation from 0) is calculated on a Day basis. I do not want the Rank per day, I would like it across the entire period.


      Does anyone have any Ideas?