- 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.
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.
Guest Spotfire blogger residing in Whitefish, MT. Working for SM Energy’s Advanced Analytics and Emerging Technology team!
This was a huge help, thanks!
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
Hi John,
Thanks for asking but that’s something I would need to see data for to troubleshoot.
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
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.