create a card to display a number ranking

ScottLeeds
ScottLeeds Member
in Cards, Dashboards, Stories

can anyone point me in the right direction on how to create a card with a number ranking that will auto update? we give the sales people a score based on their activities. the one card will put them in order based off their activity score total. how do I create a separate card to just populate their Activity Score Rank? (i.e. 1st 2nd 3rd 4th 5th or 6th) the card I have now, I have to manually update. looking to automate it

Screenshot 2023-11-15 170552.jpg

Tagged:

Best Answer

Answers

  • GrantSmith
    GrantSmith Coach

    You can use a window function for something like this:

    SUM(SUM(1)) OVER (ORDER BY `Score` DESC)

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

    I hope I'm saying this correctly. The column I'm trying to use is a beast mode so it's not showing as an option. To put in the actual column name instead of the `Score` example you gave.

  • jessdoe
    jessdoe Contributor

    @ScottLeeds you can copy the score beast mode and paste it into Grant's formula.

    sum(1) over (order by (paste your beastmode here enclosed in parenthesis))

    Also, make sure you have card interactions set up on the lead card so that the rank card knows to update with the salesman clicked on in that card.

  • jessdoe
    jessdoe Contributor

    You probably want:

    sum(1) over (order by (beast mode formula)desc)

    otherwise it will rank the lowest score 1st rather than last.

  • ScottLeeds
    ScottLeeds Member

    @jessdoe now it just populates whatever number I put in the parenthesis after sum

  • jessdoe
    jessdoe Contributor

    @ScottLeeds if you aggregate the sum again as in Grant's formula does that give you your rank back?

    sum(sum(1)) over (order by (beast mode formula)desc)

  • ScottLeeds
    ScottLeeds Member

    @jessdoe that didn't work either

  • jessdoe
    jessdoe Contributor

    Can you paste your beastmode @ScottLeeds

  • ScottLeeds
    ScottLeeds Member
    image.png

    @jessdoe

  • ST_Superman
    ST_Superman Domo Employee
    Answer ✓

    @ScottLeeds You need to use two aggregations for this to work.

    SUM(SUM(1)) over….

Categories