Calculate median without 0-valuesAnswered
I'm trying to get median calculated and it's always returning me 0 as a result. I found out that I have lots of 0 values in my data and I'd like to filter those out of the median calculation?
I can do that in SQL but I couldnt find a way to do that in explore. Is there a way to do it?
When working with metrics, you can always filter it to eliminate the 0 (zero) values on it.
To do it please navigate to Result manipulation>Metric filter
When you are in the Metric filter, you will be able to move the selector from zero to 1, Which will eliminate all elements that are resulting in zero in your metric
Please note in case you have higher values, and you move for example from 0 to 5 all results lower than 5 will be eliminated.
Please refer to this article to understand better how this will work.
Selecting the metric result range
Hope it responds to your question and feel free to send us any other you may have.
Wish you have a great day!
Thanks for the response. I actually tried that already but it didnt work for me.
I tried it first with data sorted by ticket ID, it worked good. This is result without filtering:
This is result after filtering:
So there it goes well. But when using median without filter it gives me 0
With Filter I'm expecting to see around 2 minutes and 23 seconds but what I see is just blanc white report
I have the same problem. I want to display a KPI of a median for "Requester wait time" excluding 0 from calculation but it's seems impossible...
Hi @... and @...,
I can confirm this flow won't work for time metrics.
For those metric, you will need to create a calculated attribute using this formula
IF (VALUE(Requester wait time (min)) =0)
ELSE IF (VALUE(Requester wait time (min)) =NULL )
Then add this new attribute to Filters in your query and filter out "0" and "null"
It will return the result you expect. If you don't intend to filter out zeros, you can edit that part of the formula and only leaves the Null part of it.
Please feel free to ask any questions you may have.
Hope you have a great week,
Thanks Daniel, this works great for me!
Please sign in to leave a comment.