How to get Top 2 items by Each Category

Hey everyone,

I’m really liking the Top N feature, but I’m stuck on something. Right now, when I use it, it gives me the top items overall, without splitting them up by category.

What I want to do is find the top N items in each category separately. For example, if I’m looking for the top 2 high-revenue items in each category, here’s what I expect:

Category Items
C1 I1.1
C1 I1.2
C2 I2.1
C2 I2.2
C3 I3.1
C3 I3.2

But right now, what I’m getting is more like this:

Category Items
C1 I1.2
C3 I 3.2

Any thoughts on how I can make this happen? Thanks a bunch!

1 Like

Hi @Ling

Welcome to our community :smile:.
The current Top N filter (in your case) will get the top 2 items with highest revenue (and doesn’t partition by the category).

In this case, I suggest that you should use our rank() function.
For more information, you can refer to our Guide here.

By using the rank function, you can specify the partition.

For example, I want to find the 3 most sold products within a Country

I will first create a metric to get the rank of the Product by its Sold Quantity, and Partition by Country Name

rank(order: sum(order_items.quantity) | desc(), partition: countries.name)

And then, I will filter that Rank to Less than 4. You will get the result like below

Hope this helps!

2 Likes

Oh thanks! This is exactly what I need :clap:

2 Likes