Skip to content

    • Beast Mode

    Beast Mode

    RANK Issues

    Member
    in Beast Mode

    Hi I'm trying to create a Rank, but sometimes it works others it doesn't don't know why, I have customers and I want to be able to filter by the top 5 customers based on Shipments I have tried multiple formulas and the validation works, sometimes the table works but the filter does not work:

    This are the formulas I have tried

    RANK() OVER ( ORDER BY (COUNT(DISTINCT SHIPMENT_NUMBER__TKNUM)) desc)

    and

    RANK() OVER (PARTITION BY (SOLD_TO_NAME) ORDER BY COUNT(DISTINCT SHIPMENT_NUMBER__TKNUM) desc)

    Neither works, it is kind of weird since if i put it in the table it sometimes works, others don't and it never works when i Try to filter the top 10.

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Best Answer

    • Coach
      Answer ✓

      @Marie_Serrano Each row/column combination used in a card are treated as a "row" from the perspective of the Limit Rows function. If you are using both rows and columns in a pivot table, then I suggest adding a Rank column in the MagicETL using the Rank & Window tile and filtering by that rank instead. The only drawback of this method is that the ranking will not dynamically change when users change the card filters.

    Answers

    • Coach

      @Marie_Serrano Your logic is sound, but it is likely not working because Domo does not allow you to filter by beast modes that use window functions. I suggest sorting your card by COUNT(DISTINCT `SHIPMENT_NUMBER__TKNUM`)  and using the Limit Rows function in Analyzer to only show the top 10

    • Member

      @MichelleH maybe you can help, the issue I have is that I have a pivot table and I need it to show multiple years, that i have right now in Columns. when i select the limit for the 10 rows, for some reason it only shows one year, and does not show any more all the columns.

    • Coach
      Answer ✓

      @Marie_Serrano Each row/column combination used in a card are treated as a "row" from the perspective of the Limit Rows function. If you are using both rows and columns in a pivot table, then I suggest adding a Rank column in the MagicETL using the Rank & Window tile and filtering by that rank instead. The only drawback of this method is that the ranking will not dynamically change when users change the card filters.

    • Contributor

      @MichelleH

      I applied the RANK function in Magic ETL, but it is not dynamically assigned in the Mega Table due to the applied filters. How can I get it to work on specific columns of a Pivot Table and ensure it is exported in the same format?

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Quick Links

    Categories