Replace "no data" with "0" in a dashboard

[Malaik]

Hello All,

in the tile bellow, how can I replace "no data" by "0"?

Thanks for your help and have a good day.

[a_chiaravalle]

Hi

I have the same question

Have you solved this problem in some way?  

thanks 

Antonello

[Malaik]

Hi,

Not yet work in progress, but the support it seems that not possible t this time.

[Alejandro · Accepted solution]

Hi, 

In the Data Explorer there is the option of using the :default operator. This fills empty values with an indicated one, ie: :default(0).
However this only works if the specific metric has any values in the selected timeframe. If not, the current behaviour is to display a No Data message and no 0 value can be displayed.

Regards

[Malaik]

Hi @Alejandro 

This is not logic,

I Used :default(0) before and its not work.

If the specific metric has any values in the selected timeframe, for sure the value will be different from 0 even we are not using the :default(0).

Thanks

[PedroDeodato]

I'm having the exact same issue...

Still no updates on this one ?

Thanks!

[Malaik]

Hello @PedroDeodato 

Still the same.

[echwallah]

I tend to think "NO data" semantically means there is no values coming in for some reason. Providing 0 as the default implies we have values but the value is zero. Zero is just a value like any other 1,2,3 and so on. "NO data" means something different in my opinion

[EnriqueOriol · Accepted solution]

"NO data" means there's no value tracked for the given timeframe, and it makes sense to show it this way instead of displaying 0 value, otherwise it will be misleading and you might think there's data reported during that period while that's not the case.

On the other side, the default(0) operator replaces all NULL values by 0. It is completely different because in this case there is data ingested for the given timeframe, but for whatever reason, the value attached to those events is empty.

[PedroDeodato]

Hey, @EnriqueOriol ,

I understand the "NO DATA" meaning.

However, there are some use-cases where a null does have a practical meaning, when translated to a zero.

A Client of ours uses a Custom Extension that finds the number of ocurrences of a given event using a SQL query.

When the extension can't find any, it doesn't inject a zero: it simply does not inject anything.

In this case, of a Custom Extension developed by the Client, a way to "mask" the null as a zero would be helpful to others who read the info on the dashboard.

As you stated, the :default(0) transformation only works if there has been data ingested for the given timeframe.

As it is optional to place, couldn't this transformation work when there is no data for the timeframe, as well?

Then, it would be a designer's choice: to show it as "no data" or as a zero.

Our only statement is that there should be a way to treat the missing data as expected behaviour: replacing it with a zero should be an option, in our honest opinion.

[EnriqueOriol]

Hi Pedro,

I agree there are some use-cases where a null does have a practical meaning, when translated to a zero, that's why we have the default operator. However, the problem in this case is that your data do not have a null. There's simply no data at all.

Going to the details, the thing is that we work with time-series, and we cannot know at which time should we add a zero unless there's an event with its associated timestamp (even if the event value is null).
Even if you are aggregating the serie into a single value (as is your case), your serie has no events at all, so there's basically nothing, that's not a null value.

I'm sorry but this is how metrics are intended to work. If this customer extension is reporting the number of occurrences of a given event, and they want to track occurrences of "zero" events, then they need to report those zeroes as well (or ingest a null event at least).

[arora138]

Hi Enrique,

I also am getting no data, but it's a builtin metric, builtin:kubernetes.pods with a dimensional filter to include only Pending pods. This metric should return to 0 if there's no pods but it only displays data when it's not 0. This makes it difficult to use it for equations of taking the number of desired pods - ready pods - pending pods.

If there are no pending pods, I assume the data should be null, but default(0) seems not to be working. Any insights would be very helpful. 🙂 Also if it is not getting data in and therefore there are no null values, could you explain why that's the case for this metric. Seems something to always be reporting on.

[WesL]

While I understand the approach you are putting forward, let's look at it from a customer-centric response.  We, the Dynatrace Community, are telling you that there is a need for no data = 0.  In my case, when designing an executive dashboard, I would want the data across the dashboard to be uniform and show zero with a green indicator instead of "no data" which makes it honestly look like Dynatrace isn't working.   Please reconsider the approach. 

[PedroDeodato]

Hey @EnriqueOriol ,

I think that @WesL's and @arora138 's use cases are very valid and I can relate to both pretty well.

I now also understand your explanation on the difference between data points with NULL values and no data point at all, and the difficulties that a "no data point" brings to the application of a default(0) operator, for example.

However, the need still stands.

The executives that @WesL mentioned still need some neat to handle this.

And I think the term "handle" is really the best one on this case: we get it that "this is how metrics are intended to work", but what if "how metrics are intended to work" do not solve the Users needs?

When a program spits out an Exception, it's not the end of the world... we just need to figure out a way to handle it 🙂

Some suggestions will follow:

1. In the case of a Single Value data explorer, wouldn't it be simple to just interpret a null value = no data point at all = zero ? Again, it should be optional and could even be an option specific to the Single Value chart, or others that to not require to show timestamps on data points (eg. top list, table, etc). It would be more natural to a regular (not proficient) user (like an executive): "start at zero and then sum up from there". 
2. In visualization that need to show data points (eg. graph), the default operator could be a bit more advanced, with the addition of just one parameter: frequency. So, something like :default(0,1m) would "create"/"simulate" 'fake'/'dummy' datapoints, with the frequency of 1 minute with the default value of 0, whenever the metric didn't show any data points on the timeframe. NOTE: these 'fake'/'dummy' datapoints wouldn't be stored anythere... would just need to be rendered in the visualization, to fill in gaps and show the desired zeroes whenever need be.

Just two ideas!

Maybe the best solution is something completely different: and I'm 100% fine with it.
What I defend the most is that we do not settle at "this is how metrics are intended to work", because sometimes users need something that goes beyond how something was intended to work... that's what makes us evolve 🙂

[EnriqueOriol]

Hi @PedroDeodato , the problem is that, if we don't have any data for the given timeframe, displaying a zero instead of a "No data" message might be misleading because there's no guarantee that your query is really valid.

I mean, taking @arora138 example (built-in metric with some filter that returns no data for the whole selected period). In that case we are basically getting no results from our database. We can display an array of zeros and it would be meaningful for you, for sure, but... imagine the same query with a typo in the filter. In that case, we will get as well, no results from the database, but I don't think an array of zeroes would make any sense in this specific scenario. On the contrary, you would probably want to know that something is wrong.
As a workaround for your concrete scenario @arora138, you can always use a calculated metric to make sure you are displaying zeroes, as long as the aggregated metric is always ingesting data from at least one host. However, you will be in the situation explained before, if there's a typo in the filter value you write, we will not know.

I mean, you can do
( (built-in:metric * 0) + built-in:metric:filtered:auto:default(0) ).
The :auto:default(0) here provides you with zeroes matching the first metric, so the calculation can be executed.

G.e. 
(builtin:kubernetes.pods:splitBy()*0 + builtin:kubernetes.pods:filter(and(or(eq("k8s.cluster.name",your-filter)))):auto:default(0)):sort(value(auto,descending)):limit(10)

[allison_almeida · Accepted solution]

Best aligned on this links:

https://community.dynatrace.com/t5/Dynatrace-product-ideas/Data-Explorer-Change-No-Data-for-calculat...

Guess the best approach:

"... would just enrich the :default transformation with an optional parameter to force the value even if there is no datapoint. For example: :default(0, force) or something like that ..."

https://community.dynatrace.com/t5/Dynatrace-product-ideas/Data-Explorer-Change-No-Data-for-calculat...

[DavidFearns]

Can you change the 'No Data' result when searching on metric counts in a given timeframe to show 0 (zero) instead.

I use the Code function to show Failed Request Counts filtered across different dimensions (DC, Service Procedence, User Agent) and create calculations with them, if the result is 'No data' then my calculation does not work. If it can be changed to a zero then it will work.

Thanks,

Dave

[jegron]

Hi,

Exactly same use case for me :

Really annoying for custom KPIs.

[zietho]

No data and 0 mean two completely different things. 

• No data: means not even a single data point within the selected timeframe
• 0: means a proper aggregation of at least one value in the selected timeframe. 

So "-" actually shows that there was no data or that any arithmetic operation would not have made sense. Nevertheless, I agree that for expressions it would be handy to be able to define "neutral" operations. E.g., when Dimension a = 220 and Dimension b = "-"... 

But for the record - What would you expect, for example, when doing the following simple arithmetic operations: 

• a + b = ? 
• a * b = ?
• a / b = ? 
• a - b = ? 

[DavidFearns]

If a = 220 and b= "-" (or zero) then I would want to see the below results:

• a + b = 220
• a * b = 0
• a / b = Error
• a - b = 220

and to give an example of a success rate I use in my charts:

(success count/(success count+failure count))*100 = success rate

so if sc = 220 and fc = 0 then:

(220/(220+0))*100 = 100% success rate.

Currently with the 'No data' result this happens:

(220/(220+No data))*100 = Error 

[zietho]

Thank you a lot @DavidFearns 

Have you tried applying ":default(0)" to your metrics in all the "-" cases? Given that there was at least 1 single data point reported in the timeframe selected, it should then make a dash to a value or 0. This also helps to calculate the value. Find more about it under "Replacing missing values like a bo$$" here: https://community.dynatrace.com/t5/Dashboarding/Dashboard-Data-explorer-tips/m-p/179979 

In the case no data was reported within the timeframe, we, the dashboarding/Data explorer team. can't do a lot here other than allowing for different representations, Therefore in such cases I have to  forward you to my colleagues at the metrics team. @fcourbon promised to have a look at it, whether also in such cases can provide a true default value that is then used in calculations. 

regards

[DavidFearns]

Hi, by using :default(0) it provides an incorrect answer to the calculation, @fcourbon can you help please?

It seems very inconsistent as for some of my metrics where there are no requests within the same time period the result shows as '0', but for some others it shows as 'No Data'.

Thanks,

Dave

[zietho]

I believe, please check this by switching to a "line chart" for a given metric, because for these metrics with 0 you actually ingest numbers. Probably 0 or near 0 then then is averaged to 0 and therefore folded (aggregation over all datapoints in the selected timeframe to a single one) to 0. 

This means when in the selected timeframe for the table you switch to a line chart you should see..

• no data: if there truly were no data points reported/ingested, or 
• dots or a line if any data point was reported within the time frame
  • dots if "connect gaps" is disabled
  • a line if "connect gaps" is enabled 

pls @fcourbon chime in if any correction to this is needed. 

[DavidFearns]

Hi, it works fine for line graphs but I need to use the 'Single value' option to show a success rate

[fcourbon]

Hi @DavidFearns , I had a chance to check and the default(0) should replace missing data with 0 so calculations can actually take place. For a Table or Single value visualization, make sure you are selecting the fold transformation that fits your needs, the Auto might not yield the expected results.

I am showing below a made up example where there is no intersecting data yet the calculations are taking place thanks to the default(0) and selecting Average for the fold transformation.

[Julius_Loman]

@fcourbon anyway we still need to have a transformation to set default value even if there is no data point. Just when displaying any counter.  Try to explain it to the management, they are seeing "No data" instead of 0 on the dashboard.

It applies even for the builtin metrics, for example reporting the application errors:

builtin:apps.mobile.reportedErrorCount
:filter(and(or(in("dt.entity.mobile_application",entitySelector("type(mobile_application),entityName.equals(~"easyTravel Mobile (mainframe)~")")))))
:splitBy()
:sum
:default(0) 

Just look at this thread for examples of other situations. There is definitely a need to force a default value, even there is no datapoint.

[zietho]

@Julius_Loman. I guess both, @fcourbon and I agree. It's not that we don't see a "need" for this but that there is two distinct parts to this and not both are equally easy to achieve in terms of effort.

1. How do you display a result for simple metrics (table, single value) that's something we can provide in the Data explorer and should fix most likely the majority of cases? E.g, have an input field saying "default.. " that allows you to put whatever, for example, "$".  
2. How do you display a result of metric expression? Here we need @fcourbonbecause we can't solve this in the Data explorer 

for 1, however, there is still one central question: Where would any such default make the most sense to be set?

• For each tile in the Data explorer? (flexible but tedious if you need to do this often)
• Or, on a dashboard level (fast but very limited for different metrics) 

[Julius_Loman]

@zietho My two cents:

1. Input field is OK for the Data Explorer common use case and easy to use by non Dynatrace experts. In most situations I would just put zero (0) , maybe dash (-) - just for displaying.
   
   
2. If possible, I would just enrich the :default transformation with an optional parameter to force the value even if there is no datapoint. For example: :default(0, force) or something like that. This would solve also 1) unless you want to display non-numeric value.

I would not try to solve it on a dashboard level and leave it to an individual data explorer tile.

[jegron]

Totally agree with @Julius_Loman use cases.

I would add that in a supervision logic, it is painful to see a host or a tile disappear because the datasource is temporarily unavailable.

[Tom_Eaton · Accepted solution]

You can now use defualt(0,always) 
Use the metric selector (Data Explorer Advance) to use it. - it has to come after a splitby()

Doc: https://www.dynatrace.com/support/help/shortlink/api-metrics-v2-selector#default

[cabeywardhana]

@Tom_Eaton is there something similar available for DQL in Grail ?

[Tom_Eaton]

Hello @cabeywardhana,

Yes there is! We can use a combination of the nonempty and default parameters. See the below screenshot from the documentation:

https://docs.dynatrace.com/docs/platform/grail/dynatrace-query-language/commands/metric-commands#exp...

[cabeywardhana]

Thank you for the quick reply Tom.  I have tried your solution and my scenario looks a bit different.  
I have asked a new question here for my scenario. Thank you again.