Dynamic Rank Beast Mode

rockchalk629
rockchalk629 Member
edited November 2022 in Beast Mode

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?

Tagged:

Comments

  • GrantSmith
    GrantSmith Coach

    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.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • rockchalk629
    rockchalk629 Member
    edited October 2022

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

  • yentran
    yentran Member

    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."

    image.png

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


    Appreciate your help.

    Thanks,

    Yen

  • jdorsch2
    jdorsch2 Member

    @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!

    1. 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!

Categories