13 Replies Latest reply on Oct 25, 2016 6:59 AM by Nikunj Bhardava

# How to get minimum non zero non negative value from a field

How to get minimum non zero non negative value

report     execution time

aa          20 sec

aa          10 sec

aa          30 sec

aa          0

output shall be 10 sec but tableau returns 0 or null if I apply above approach on doing min which is obvious. but I wish to see the minimum  last non zero non negative value.

• ###### 1. Re: How to get minimum non zero non negative value from a field

you can always use min() function.

first filter out negative values & show the min of the remainder values else 0

• ###### 2. Re: How to get minimum non zero non negative value from a field

Create a calculated field to remove all equal to or below 0

IF [ExecutionTime] > 0 THEN [ExecutionTime] END

That will convert everything 0 or below to NULL. Take the MIN of that field, it should work. If not then you could always change the formula to convert all of the 0 and -ve values to be a very large +ve number. For example: IF [ExecutionTime] > 0 THEN [ExecutionTime] ELSE 1000000 END

• ###### 3. Re: How to get minimum non zero non negative value from a field

to explain scenario further:

input data

report     execution time

aa          20 sec

aa          10 sec

aa          30 sec

aa          0

Expected output report

Report name      Avg Execution Time      Max Execution Time      Minimum Execution Time

aa                       20 sec                            30 sec                            10 sec

here average is average of three non zero values, and minimum is among those three only.

• ###### 4. Re: How to get minimum non zero non negative value from a field

Hi Ankit,

PFA workbook for your reference. let me know if it helps you or not.

Thanks,

Nikunj

• ###### 5. Re: How to get minimum non zero non negative value from a field

I think you can add the Min() to Andrew's calc above. And then just use that field as it is instead of taking the min of it. Probably helps with process to have 1 calculated fields vs two. See attached and below.

• ###### 6. Re: How to get minimum non zero non negative value from a field

IF [ExecutionTime] > 0 THEN [ExecutionTime] END

this is giving me nulls in case when I have 0 records

this sweet hack solved one of my issues to get the Minimum non negative non zero value exactly, Thanks a ton, I was thinking same but was not trying out, but now that I have tried, it worked fine:

If not then you could always change the formula to convert all of the 0 and -ve values to be a very large +ve number. For example: IF [ExecutionTime] > 0 THEN [ExecutionTime] ELSE 1000000 END

one issue left is the average part to have

input data

report     execution time

aa          20 sec

aa          10 sec

aa          30 sec

aa          0

Expected output report

Report name      Avg Execution Time      Max Execution Time      Minimum Execution Time

aa                      20 sec                            30 sec                            10 sec

• ###### 7. Re: How to get minimum non zero non negative value from a field

this seems to be corrupt, its not opening up.

• ###### 8. Re: How to get minimum non zero non negative value from a field

Average time: SUM(IF [ExecutionTime] > 0 THEN [ExecutionTime] END)/SUM(IF [ExecutionTime] > 0 THEN 1 ELSE 0 END)

• ###### 9. Re: How to get minimum non zero non negative value from a field

Which version are you using? I created it in V10

• ###### 10. Re: How to get minimum non zero non negative value from a field

Hi Ankit,

See this snapshot.

I created 3 same calculations like

1. MinValue

2. MaxValue

3. AvgValue

All the above calculation contains same formula. you just need to place as shown in snapshot.

Thanks,

NIkunj

• ###### 11. Re: How to get minimum non zero non negative value from a field

Thanks Andrew, It worked as expected. I was simply overlooking something obvious.

• ###### 12. Re: How to get minimum non zero non negative value from a field

Thanks a ton Nikunj !! Nice solution, it helped really !