Rank the row but without duplicate

Q&A
,
1

Hello,
I am doing the follwoing
denserank([{total fee per order} DESC],[{service_code},truncDate('MM',{order date})],PRE_AGG)
to calculate the rank.
However the problem is that I have many orders with the same {total fee per order} thus they will end up at the same rank.

I would like to avoid the same rank since after I want to filter all the orders with rank higher than a certain value in my values.
any idea how to work around to this?
thank you

2

Hi @remba87 Thanks for your question.

To avoid having the same rank for orders with identical total fees, you can modify your DENSE_RANK calculation to include additional tie-breaking columns:

  • Add the order ID or another unique identifier as an additional sorting criterion, sample:
    [{total fee per order} DESC, {order_id} ASC]
  • If you don’t have or don’t want to use an order ID, you can combine multiple columns to create uniqueness, sample:[{total fee per order} DESC, {order date} ASC, {customer_id} ASC]

The key is to add enough sorting columns to ensure uniqueness in the ranking. By adding additional sorting criteria after the {total fee per order}, you’ll get unique ranks even when the total fees are identical. Make sure to choose tie-breaking columns that make sense for your business logic and use case.

Hope this helps!
I am marking this reply as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the Quick Sight Community!

1 Like