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?
You can use EXCLUDE LOD function that will exclude RANK from ranking DAY
e.g.
{EXCLUDE Date(Days): SUM(Sales)}