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



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

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


Best Answer

  • Misbah
    Answer ✓


    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


    Miz Logix




  • @ymugeni 


    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.



    Miz Logix

  • 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.

    Screenshot 2022-10-18 124617.png

  • 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.

    Screenshot 2022-10-19 at 12.10.36 AM.png

    Screenshot 2022-10-19 at 12.10.48 AM.png


    - Shubham

  • @ymugeni 


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




  • 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. 

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

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




  • @ymugeni 

    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


  • Ok, will do. Thanks.

  • Rnilsson
    edited November 2023

    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