Skip to Content
Jan 27, 2021 at 05:30 PM

Webi excluding values from Rank


I am trying to create rank, excluding some rows in the calculation based on another object in the table.

The object AnalyteValue is a string. I am using the ToNumber function to create an object in the number format.
A side effect of doing this is that sometimes the AnalyteValue can be ND or empty, and in these cases the ToNumber function obviously is given error when doing this. This is not necessarily an issue for the report.

When I create a Rank on AnalyteValueNumber, the output is the following:

This is not what I want. I need that the columns where there is an #ERROR to be ignored in the ranking. Therefore I adapted the Rank formula with a Where clause:

=Rank([AnalyteValueNumber];Top;[AnalyteName]) Where ([AnalyteValue]<>"" And [AnalyteValue]<>"ND")
The result is then:

As you see, my formula is not behaving the way I was expecting.

The actual result should be:

Any help in defining the correct formula for my requirement is needed, as I am unable to find the correct formula to make it work.

Note the end goal is to do some conditional formatting based on the Rank, but I still need the AnalyteValue listed where this would ND or empty, so excluding these from the table is not an option.


hifi0.png (5.4 kB)
qytph.png (5.6 kB)