RANK Issues

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

Best Answer

  • MichelleH
    MichelleH 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

  • MichelleH
    MichelleH 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

  • Marie_Serrano
    Marie_Serrano 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.

  • MichelleH
    MichelleH 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.

  • art_in_sky
    art_in_sky 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?

Categories