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

ymugeni
ymugeni
in Modeling

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:

Best Answer

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

     

     

Answers

  • Misbah
    Misbah

    @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

  • ymugeni
    ymugeni

    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

  • ShubhamCh
    ShubhamCh

    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

  • ManjunathKN
    ManjunathKN

    @ymugeni 

     

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

     

    Thanks,

    Manjunath 

  • ymugeni
    ymugeni

    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. 

  • ymugeni
    ymugeni

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

  • ManjunathKN
    ManjunathKN

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

     

    Thanks,

    Manjunath

  • ShubhamCh
    ShubhamCh

    @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

     

  • ymugeni
    ymugeni

    Ok, will do. Thanks.

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

Categories