Dense Rank - How can I exclude null values while ranking


I have a denseRank calculated field as below,

How can I exclude the null values in data by keeping the level aware aggregation? The null values are now assigned Rank 1.

Earlier I had a filter in visual level to skip those fields. As I need to apply PRE_FILTER aggregation now in denseRanking, the ranking is getting affected because of the null values

I am a newbie in Quick Sight. Any help would be much appreciated

Hi Philip - Are you able to use PRE_AGG instead of PRE_FILTER? Then it will rank after your filters are applied.

Alternatively maybe you can move the filter to take out the NULL values into the Data Set preparation page? Those will happen even before PRE_FILTER.

Sorry for my late reply. Thanks Jesse for the valuable suggestion.

However, PRE_AGG didn’t work out in my scenario. The second solution of creating a filter in the Data Set preparation process will result in excluding those entries completely. I need those entries for some other visual purposes. It’s just in the ranking visual I need to skip them.

Anyways for the time being, I tried a similar approach like assigning a bigger value for the ‘timetaken’ for null cases even before the Data preparation stage. This will then result in a bottom rank for those particular cases. Then I filtered the visual to exclude those which have that “bigger value”

Ah I see. Cool the solution you came up with seems like it will work. Let us know if you need anything else!