Get Top 5 category values for each date from category

chetan_cricut · 2023-02-08T06:35:05+00:00

There was an error rendering this rich post.

I have a column "category" which has more than 10 categories for each date. I would want to see only the Top 5 category for each date. Can I get a beast mode or a work around for the same.

I have attached an image for reference.

Find more posts tagged with

Sort

Accepted answers

MarkSnodgrass

You can do this by using the rank window function in a beast mode and then adding it to the filter to filter where rank is less than or equal to 5.

RANK() OVER (PARTITION BY WEEKOFYEAR(`date`) ORDER BY SUM(`metricfield`) DESC)

If you don't have window functions for beast modes turned on in your instance, you will need to have your CSM enable it.

All comments

MarkSnodgrass

You can do this by using the rank window function in a beast mode and then adding it to the filter to filter where rank is less than or equal to 5.

RANK() OVER (PARTITION BY WEEKOFYEAR(`date`) ORDER BY SUM(`metricfield`) DESC)

If you don't have window functions for beast modes turned on in your instance, you will need to have your CSM enable it.