6 Replies Latest reply on Nov 27, 2018 5:39 AM by Kevin Warren

# LOD - Calculate Min Value That is Not 0 or Null

I am trying to arrive at the lowest non null or 0 value for my chart (see DOWN ARROW) , without excluding dates from my x axis.

This is what I have so far:

UTILIZATION (MIN) =

IF(NOT ISNULL(WINDOW_MIN(AVG([Utilization Pct]),FIRST(),LAST()) )

OR WINDOW_MIN(AVG([Utilization Pct]),FIRST(),LAST()) > 0 )

THEN WINDOW_MIN(AVG([Utilization Pct]),FIRST(),LAST())

END

• ###### 1. Re: LOD - Calculate Min Value That is Not 0 or Null

Good day Kevin.

Would an include LOD work?

MIN({include : AVG([Utilization Pct])})

Also, if you're only displaying the value (I suspect you need it programmatically, but just in case), under the Marks Card -> Label will allow you to display only high/low points.

Best regards,

-Dan

• ###### 2. Re: LOD - Calculate Min Value That is Not 0 or Null

Thanks for your response. I tested your calc and it did not work for me but maybe it will add context. In the below picture, I am striving for my "Utilization (Min)" field to return 2.06% in this situation and not 0% like it is.

• ###### 3. Re: LOD - Calculate Min Value That is Not 0 or Null

Looks Like You are Looking for a Single Minimum Value from Entire data Set

• ###### 4. Re: LOD - Calculate Min Value That is Not 0 or Null

Good day Kevin.

Okay, try this calculation:

{fixed:

MIN(IF{fixed DATETRUNC('day', [Date]): AVG([Utilization Pct])} > 0 THEN

{fixed DATETRUNC('day', [Date]): AVG([Utilization Pct])}

END)

}

AND right-click the Date in the filter pane and select "Add to Context".

Does this help?

-Dan

• ###### 5. Re: LOD - Calculate Min Value That is Not 0 or Null

WINDOW_MIN( if avg([Utilization Pct]) > 0 then avg([Utilization Pct]) end, first(), last())

set to compute using table down

1 of 1 people found this helpful
• ###### 6. Re: LOD - Calculate Min Value That is Not 0 or Null

Thank you very much!