CODEWITHSUNDEEP
tableau-apicalculated-field
Gregology
Gregology

Reputation: 1735

Second Max in Tableau Calculated Field

How can I get the second highest value from a field in a calculated field. In excel I would use the large function but there doesn't seem to be a tableau equivalent. I would prefer to do the calculation in Tableau instead of using a pass through function.

Upvotes: 0

Views: 9839

Answers (1)

Alex Blakemore
Alex Blakemore

Reputation: 11921

Here are two alternatives.

First, if you want the calculation to happen on the data source side, You could write a LOD calculation to find the max of your field, name it myMax

{fixed [My_Dimension1], [My_Dimension2] : max(myField)}

Whether you use fixed, include or exclude scope for the LOD calc depends on how you want to scope your analysis.

Then write a row level that returns the field value if it is less than the LOD calc, and implicitly null otherwise, name myFieldExceptMax

if myField < myMax then myField end

The max of that row level calc would be your answer.

max(myFieldExceptMax)

Alternatively, if you want to operate on the client (tableau) side to find the penultimate aggregated query result, you can use on of the ranking table calc functions, and the filter to only show the second ranking result.

Upvotes: 2

Related Questions