# Tableau Calculated Field - across multiple measures

Cheyfi

I am trying to take the minimum of a set of fields, excluding 0's. I am fairly new to using calculated fields in Tableau.

I have been able to chain the min function like so:
`MIN(MIN(MIN([a],[b]), [c]), [d]) etc...`

However, I can not figure out a way to filter out the 0s for every one of my measures. I tried something with REGEXP_REPLACE. I also tried to set a larger IF THEN for the values with something like:

``````IF [a] > 0 THEN [a]
IF [b] > 0 THEN [b]
``````

So that the new values would be nulled out. But, this didn't work either due to my need to chain all of them in this one calculated field. What is a functional way to calculate something like this? Assuming I can not modify the data outside of Tableau, what is a way to handle modifying multiple fields like this?

Desired Result:
(Pseudo Code)

``````given that a,b,c,d,e = 15,0,10,0,25
Min_No0([a],[b],[c],[d],[e],...) = 10
``````
SEAnalyst

Here is a solution that looks for the MIN() between columns [a] [b] and [c], excluding and values that are zero.

``````MIN(MIN(IF [a] !=0 THEN [a]
ELSEIF [b] !=0 THEN [b]
ELSEIF [c] !=0 THEN [c]
END,

IF [b] !=0 THEN [b]
ELSEIF [c] !=0 THEN [c]
ELSEIF [a] !=0 THEN [a]
END),

IF [c] !=0 THEN [c]
ELSEIF [a] !=0 THEN [a]
ELSEIF [b] !=0 THEN [b]
END)
``````

The inner MIN() is compares [a] and [b]. If either of these numbers is zero, it substitutes another number, provided that number is also not equal to zero. The outer MIN() compares [c] to the smaller number from the inner MIN().

If [a], [b], and [c] are all equal to zero, the result will be 'Null' of course.

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at

0 comments
Login to comment