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)

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.

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.

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?

• ###### 1. Re: Table Rank Calculation across entire period and not by day (Granularity of the View)

You can use EXCLUDE LOD function that will exclude RANK from ranking DAY

e.g.

{EXCLUDE Date(Days): SUM(Sales)}