create a card to display a number ranking

ScottLeeds

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

ST_Superman

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

SUM(SUM(1)) over….

GrantSmith

You can use a window function for something like this:

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

ScottLeeds

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

@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

You probably want:

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

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

ScottLeeds

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

jessdoe

@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

@jessdoe that didn't work either

jessdoe

Can you paste your beastmode @ScottLeeds

ScottLeeds

@jessdoe

ST_Superman

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

SUM(SUM(1)) over….