Top N rows after grouping the table by a field

Question & Answer
, ,
1

Hi Team,
I have a schema like below:
|Department_id| Customer_id| order_value| order_month|

Now I want to find top 5 customers(as per the total order values) in each department using a quicksight pivot table. How to do this?

Thanks.

2

Hi,

create a calculated field and use sumOver() get the order_value for a customer in a department.

Create another calculated field and use rank() to get the ranking of a customer in a department, depending on his sumOver of the order_value.

If this throws out an nesting error, move the first calculation to the dataset, so it is considered as a value in the analysis and not as a calculation.

Then add a filter for the last calculated field with the condition, that the value has to be lower than or equal to 5.

Thomas