How to add a row number that changes dynamically in Table Card

Hello, 

How can we add row number or rank in table card? 

I have attached a screenshot for more clearification. I want to display the row number in table card which has to be refreshed everytime we apply filter. Can we do it using beast mode? 

We cannot use sql or ETL as this rank has to refresh everytime the filter is applied 

Thanks

 

 

 

Best Answer

  • ST_-Superman-_
    Answer ✓

    You will need to make sure that you are sorting your table card with the same values that you include in the order by clause of this beastmode:1.png

     


    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman

Answers

  • In your image, you have rank 3 listed twice.  Was that a typo, or is there something more to the rank calculation than just row number?


    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
  • Thats actually a typo. It should be each row number refreshing every time we change the filter or use different sorting. 

  • ST_-Superman-_
    Answer ✓

    You will need to make sure that you are sorting your table card with the same values that you include in the order by clause of this beastmode:1.png

     


    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
  • What happens when there are aggregation in the columns of table chart like AVG(Salary),SUM(Age) something like that? 

  • Just change the count to be count(distinct) instead


    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
  • When I do count(distinct) it is saying that is not a valid formula.  Below is my formula.  I am counting the sales names, but against their sales and i want to sum the sales.  The first is invalid, but the second is not and the second wont allow aggregation

     

    count(distinct `SalesHost`) over (order by `NetSales` desc)   

     

    count(`SalesHost`) over (order by `NetSales` desc)

  • @ST_-Superman-_ 

    When I do count(distinct) it is saying that is not a valid formula.  Below is my formula.  I am counting the sales names named (SalesHost), but against their sales and I want to sum the sales. I added a beastmode to calculate the rank, but need to get rid of the duplicate sales names.  The first is invalid, but the second is not and the second wont allow aggregation.

     

    count(distinct `SalesHost`) over (order by `NetSales` desc)   

     

    count(`SalesHost`) over (order by `NetSales` desc)

     

  • @user14340 - Can you provide some sample data so that I can test it out with your schema?

     

    I'm not sure what is causing the issue.  I might try

    count(`SalesHost`) over (order by sum(`NesSales`) desc)

    but i'm not sure if that is valid or not.  


    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
  • Your solution worked perfectly for aggregates.

     

    Just as a side note I found that the below formula more accurately ranked with the method I wanted.

     

    rank() over (order by sum(`NetSales`) desc)

This discussion has been closed.