4 Replies Latest reply on Nov 21, 2016 5:09 PM by Bora Beran

    Ranking based on RANK_DENSE() loses continuity when applying a filter

    Dimitris Mitrodimas

      Hi everyone,

       

      I work in the Shipping industry and I have a dataset that comprises some historical data related to port performance. I have created a worksheet to show a Port-related measure. Let's call this measure Performance. What I want to do with this measure is rank worldwide ports by descending order, so I create a calculated field called Rank:

       

       

      and use it in the Rows shelf in-front of Performance, so the results are the following:

       

      In the sheet I apply an in-context filter. This filter is used to remove outliers from the dataset. Let's call it FilterOutliers.

       

      It does so by using a field of my dataset that is called Buckets. Buckets takes integer values from 1 to 20 and is  produced in SQL through the function NTILES(20) (for those that are not familiar with SQL, this function breaks a distribution in 20 equal parts), and shows in which bucket of the distribution of Performance a certain record falls. For example, a record whose Bucket=1 falls in the first bucket (that represents the first 5% of the distribution), a record whose Bucket=20 falls in the 20th bucket (that represents the last 5% of the distribution). Since I have seen that the distribution of Performance is right-skewed, I remove outliers by "demanding" that Buckets is less than a threshold, lets call it MaxBucket

       

      Now here's the tricky part. MaxBucket is controlled by a parameter which I call Confidence_Level, which has three values: LOW, MEDIUM, HIGH, and for each of these values MaxBucket takes a different value.

       

      When I set Confidence_Level to LOW, everything works fine, meaning that the ranking appears as it should, being continuous from 1 to 10 with no gaps in-between. But when I set it to MEDIUM or HIGH, it seems that the filter somehow spoils the result and the ranking loses continuity:

       

      I am trying to figure out how the filter could affect the ranking. But until now I cannot find any solutions to this. (I'm sure that the problem is with the filter because when I disable it, the ranking is fine again).

       

      I have also tried to put the filter out of Context, still didn't work.

       

      So, any ideas on how the filter affects the ranking? And how I could prevent it from doing so?