Dynamic Rank Beast Mode

rockchalk629 · 2022-10-20T18:42:02+00:00

There was an error rendering this rich post.

Does this functionality exist yet? When I try to do a rank() in a beast mode it skips from 1 to 16 every time no matter how I adjust my order by or partition, the only way rank() seems to work correctly is in the ETL itself, however I want to be able to filter the dataset and have the data rank accordingly by the filters.

What have people done to get around this?

Find more posts tagged with

Beast Mode

Comments

GrantSmith

Are you using RANK in a window function? It sounds like you don't want any missing numbers in your sequence. Rank will ignore numbers if there are ties in your dataset. In this case you might want to try DENSE_RANK instead as that won't have any skipped rank numbers.

rockchalk629

I was using a rank function. My dense_rank wasn't working yesterday but is working now so that solved it. Thanks!

yentran

Hi @rockchalk629 and @GrantSmith ,

Are you able to use RANK(), DENSE_RANK() function in Domo card design, not Magic ETL?

Since I wasn't able to do so. It said "Calculation Error : This calculation is using a nonexistent function."

Is it a beta version feature or only available based on subscription?

Appreciate your help.

Thanks,

Yen

jdorsch2

@yentran Rank and dense rank use the window function, since Domo has to know how to group the data to perform the action of ranking. I believe window functions also have to be enabled for your Domo instance, so this might not work without that!

For the window functions you typically use nested aggregate functions like SUM(SUM('column_name')) and then have to define how you want Domo to organize the data. For ranks it's a little different and I don't think you need any nested aggregates and you can just use Rank instead. Something like this should work depending on what columns you use!

RANK() OVER (PARTITION BY `column_to_create_subsets_in_your_data` ORDER BY 'column_name_to_determine_rank_order')

There are a lot of window related functions posts in Domo Dojo and they explain it better than I can haha, but hopefully this helps with why you're getting the error at least!