Get Top 5 category values for each date from category

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.

Tagged:

Best Answer

  • MarkSnodgrass
    Answer ✓

    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.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.

Answers

  • MarkSnodgrass
    Answer ✓

    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.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.