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

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?

 

Best Answer

  • Misbah
    Answer ✓

    @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

     

     

Comments

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

     

    Misbah

    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

    Thanks!

    - Shubham

  • @ymugeni 

     

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

     

    Thanks,

    Manjunath 

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

     

    Thanks,

    Manjunath

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