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

0 comments
Login to comment

Related

Is it possible to count the number of occurrences of a calculated field's result in Tableau?

Tableau calculated field summing up the values

Regular Expresion in Tableau returns only Null's in Calculated Field

Tableau Calculated Field COUNTD Single Value

Calculated Field based on an Relation Aggregate with Filter Conditions in Tableau

IF part in tableau calculated field is not getting executed

In Tableau how can I create a calculated field that categorizes data based on data across rows?

Calculated field in tableau to find if the first row is Success or Failure

Tableau Date Filter across multiple tables

calculated field in tableau dependent on values in column

Get Total Value of Bill Number - Tableau calculated field

Tableau calculated field dates

Using filtered results as field for calculated field in Tableau

SSAS Calculated Member for measures that have the same Dimension

calculated field combining multiple fields into one

Second Max in Tableau Calculated Field

export tableau to csv including measures

Tableau Pie-Chart with Measures

Tabular Query to Compare Measures Across Dates

Tableau: Create a newline between text using a calculated field

MDX syntax for DAX measures calculated with AVERAGE()

Tableau multiple charts with calculated field

Tableau Calculated Field error

Is it possible to convert categorical measures to numerical measures in Tableau?

Tableau Calculated Field using FIXED

Error in Tableau calculated field formula

Tableau calculated field distinct sum of metric base on selected dimensions

Implementing a calculated field within my Tableau Viz

Create a calculated field to count dimension on a condition in Tableau