# I am calculating a minimum value, but I don't want the data to include zeros.

Options

Hi,

I want to calculate the minimum value but exclude all zeros.

I tried previously suggested solutions, but it is still not working. Any ideas?

Tagged:

Options

@ymugeni

Create a  line item (List formatted on Client) - Line item 1 -> IF amount = 0 then blank else item(clients)

Line item 2 -> Amount[MIN: Line item 1]

Hope that helps

Misbah

Miz Logix

• Options

You need to provide more info on your query. There are a lot of open ends and need more clarity.

However, There is a MIN function that you can use as an aggregation function.

Misbah

Miz Logix

• Options

The minimum summary method does not work for me because it brings back zeros, which makes sense. I want the calculation to take into consideration the minimum of the highlighted non-zero numbers only. I tried IF statements, but since it ends with Else 0, I am still getting zeros.

• Options

Hi @ymugeni

Here is the implementation of @Misbah 's solution. Using that method you'll have "Infinity" in some cells. I have added two additional line items as solution 2. You can also have a look at those.

Both solutions work fine.

Thanks!

- Shubham

• Options

I think you can use optimizer for this. Excluding zeros and getting minimum value.

Thanks,

Manjunath

• Options

Thank you so much for your timely solutions @Misbah@ManjunathKN , and @ShubhamCh . The only issue is getting rid of the infinity, but this is good for now.

• Options

@ManjunathKN , can you expand on that? Any pictures or examples you can share?

• Options

@ymugeni if above solution helps then very well. You may go through the optimizer lesson, they have explained there.

Thanks,

Manjunath

• Options

In the screenshot shared by me, I have added 2 independent line items as a different solution. You can also use that solution. It won't show "Infinity" in the cells. The second line item - solution2 L2 is optional. You want to use it, use it otherwise use solution2 L1's summary (Min).

- Shubham

• Options

Ok, will do. Thanks.

• edited November 2023
Options

Just as a comment to this as other people may view this article.
I have used another approach to this in the past.
Given you have a scenario where you want to view zero if that is the only entry (or if there are no number entries). But this needs a few more steps:

What I have done then is to set up the following function
Line items:—————————-Formula
Numbers to review.————— (blank or your equation). summery setting MAX
Max Value —————————-Numbers to review ← Dim by parent
Zero Value? (Boolean).————'numbers to review' = 0
New Number to review.————if zero value? then max value[lookup parent] else numbers to review. Summery -Setting: Min
Min Value & avoid Zero. ———'New number to review.' ← Dim by parent

This would only generate 0 if all items where 0 else it would return the smallest value <> 0