TABLERANK

The Tablerank function returns the rank of each record within a table, based on the specified numeric field. This will allow you to sort and filter your data before ranking it, to calculate and flag the top or bottom records for that value.

TABLERANK( <Field_Name> [, <Filter_Query> [, <Reverse> ]] )
  • <Field_Name>:The Numeric Field you want to Rank your data based on
  • [, <Filter_Query>: OPTIONAL: The query to include to filter down the data included in this rank
  • [, <Reverse>: OPTIONAL:
  • "False" or "0" or null - Default - will sort and rank with highest values first.
  • "True" or "1" - will reverse the sort, so will rank with the smallest values first.

Example: create a field on the Individual table that ranks all Females in the database by their lifetime spend.

TABLERANK ( [Individual.Lifetime Spend] , [Individual.Gender] = "Female" )

Results:

Assume the database has three females with lifetime spends of 750, 999, and 25:

  • The female with a total spend of 999 is ranked 1st (1)
  • The female with a total spend of 750 is ranked 2nd (2)
  • The female with a total spend of 25 is ranked 3rd (3)