Ranking Data

[chiukim]

Hello. Im having trouble finding the right function/s to use . I am working with the lates and absences of  employees, and I want to get the list of top 20  out of those employees who have greater than or equal to 2 counts of absences, and the list of top 20 out of those employees who have at least 1  count of more than 10 minutes lates.  I want to know how to rank these data as sampled below:

[TeeSee1 · Accepted solution]

if you already have a table listing epms ordered by the number of lates, then the rank can be calculated by

min(select(ranks[_RowNumber],[lates]=[_thisrow].[lates])) - 1

You can delete rows that have lates > 20 then...

[TeeSee1 · Accepted solution]

if you already have a table listing epms ordered by the number of lates, then the rank can be calculated by

min(select(ranks[_RowNumber],[lates]=[_thisrow].[lates])) - 1

You can delete rows that have lates > 20 then...

[chiukim]

its working now. thanks. but the problem now is my table listing is not yet in ordered. I've tried this slice formula to orderby the table 1st. but isnt working. IN([s_bpartner_employee_id] , ORDERBY(late_for_ranking_office[s_bpartner_employee_id],[no_of_lates], false))

[TeeSee1]

I do not know an easy way to reorder a table using actions and expression.

One way to do it is to use a webhook to invoke an Add API with sorted data. This way you can use ORDERBY to obtain an ordered list of emp id's based on the number of lates and import it to a Ranked Emp table to accomplish what you want.

[TeeSee1]

I just became aware of this tips from a recent post. (thanks @Suvrutt_Gurjar !) which makes the whole thing quite simple. I re-post it here for the benefit of anyone who may see this Q&A.

You can directly calculate ranks without requiring sorting.

[Suvrutt_Gurjar]

Yes @TeeSee1 . In that tip the genius of @Steve  has given a beautifully compact expression for INDEXOF().

[chiukim]

can you please help me to use this formula? I dont get it. Thanks

[TeeSee1 · Accepted solution]

COUNT(
  SPLIT(
    ANY(
      SPLIT(
        (" , " & SORT(your table[no of lates]) & " , "),
        (" , " & [no of lates] & " , ")
      )
    ),
    " , "
  )
)

Specify descending? flag in SORT() as appropriate.

[chiukim]

its working thanks alot.