DOMO Rank System

user063136 · 2021-02-17T21:16:59+00:00

There was an error rendering this rich post.

I have this card that I'm trying to rank by each month.

It seems like it's ranking the numbers with both months, but I want them ranked individually by month.I.E. between 9 and 10 #9 is under Jan but #10 is under Feb.

This is the beastmode so far.

Rank()

Over(order by COUNT(`Certificate#`) desc)

Also is there a way to remove the month rankings and just do it by total if that's possible or remove the total rank column and just do it by month? Thanks!

Find more posts tagged with

Accepted answers

All comments

GrantSmith

Hi @user063136

You just need to add a partition to your window function to calculate the rank within each specific month.

RANK() OVER (PARTITION BY YEAR(`date`), MONTH(`date`) ORDER BY COUNT(`Certificate #) desc)

user063136

Rank()

Over(partition by MONTH(`EventDt`),YEAR(`EventDt`) order by COUNT(`Certificate#`)DESC )

turns out like this

jaeW_at_Onyx

i think your result is correct even though it's not what you want.

start with a table card before you do a pivot table.

sort your card by year, month, count(cert) desc in your

validate whether your original rank() is giving the correct result. if not, try

sum(min(1)) over (partition by MONTH(`EventDt`),YEAR(`EventDt`) order by sum(1) desc )

https://www.youtube.com/watch?v=Esnu1PSxRjM

user063136

Sorry I'm still not getting it. Not sure how to use a pivot table to get the ranks by month and total.

jaeW_at_Onyx

@user063136

post a sample dataset.

user063136

The Dataset format is like this, but the dates aren't limited to 2021 obviously.

Kumar619

Hi GrantSmith , How you used Rank() , its not present in Beast mode.

jaeW_at_Onyx

@Kumar619 it's a feature that needs to be enabled on the instance, contact your CSM or support and ask them to enable "window functions in beast modes"