Business Intelligence Tools

Spotfire Expressions – Rank and DenseRank

  • Have you ever wondered what the difference is between Rank and DenseRank?

The Spotfire Rank function is a very commonly used function, especially in oil and gas, as it is commonly used to calculate days on production.  There is another function listed called DenseRank that performs a similar function.  However, from the description provided in the software, it’s not clear what the difference is between then.  Both functions are described as follows: Returns an integer value ranking of the values in the selected column. The first argument is the column to be ranked.

So, I threw together a quick example to explain the difference between them.

data-table

The exact expressions used were:

Rank([Value],”asc”)

DenseRank([Value],”asc”)

As you can see, when ties happen, the Rank function sees the tie as more than one value, whereas, the Dense Rank sees the tie as a single value and continues counting.

5 thoughts on “Spotfire Expressions – Rank and DenseRank

  1. John Klein Reply

    Hi Julie,

    I’m trying to do a rank on row count, grouping by an attribute and group everything not in the top 10 as “Other”: If(DenseRank(Count([SAMPLE_NUMBER]) over ([ANALYSIS]),”desc”)<=10, [ANALYSIS],"Other").

    I want to calculate on filtered data so I'm using a Custom Expression and selecting Filtered Data Only.

    The problem is if there are ties I get a more than 10. I could live with showing 10 and 11 if tied, but if 5, 6, 7 are tied I still want 11 and 12 in "Other" but they show up as 9 and 10. Can't use Rank since many rows in the data and I get very large numbers. Seems I need Rank, not DenseRank. but on
    Thanks,

    John

    • Julie Sebby Post authorReply

      Hi John,
      Thanks for asking but that’s something I would need to see data for to troubleshoot.

  2. Nick Parrillo Reply

    I have a list of daily production data broken out by well and day date. For select dates I am getting duplicate entries due to the fact that I have some other associated data that occurred on that date to that specific well. How could I go about using the TIBCO expression:

    If(Rank(Baserowid(),”asc”,[myGroup1])=1,”First row of group”,”Duplicate”)

    but modifying it to also separate “by well”?

    Thanks

    • Julie Sebby Post authorReply

      You need another column of data to include in your rank to tell the two data points apart (the ones that occur on the same day). I would suggest doing a cumulative count of the data points per day and then including that in your rank.

Leave a Reply

Your email address will not be published. Required fields are marked *