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

Prajwal

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

 

 

 

ST_-Superman-_

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:

 

ST_-Superman-_

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?

Unknown

Thats actually a typo. It should be each row number refreshing every time we change the filter or use different sorting. 

ST_-Superman-_

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:

 

Unknown

What happens when there are aggregation in the columns of table chart like AVG(Salary),SUM(Age) something like that? 

ST_-Superman-_

Just change the count to be count(distinct) instead

Benjamin

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)

user14340

@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)

 

ST_-Superman-_

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

Benjamin

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)