How to zero-fill charts/bins in Application Insights Analytics

Question

I'm trying to plot an area chart of one sum/count metric over time in Application Insights Analytics:

customEvents
| where timestamp > ago(7d)
| summarize count() by bin(timestamp, 1h)
| render areachart  

What I see is that if there is no data in some buckets then chart doesn't drop to 0. Instead two dots are connected and there is perception that there were some data when in fact there were not.

Question - how to get zero-filled area charts (corresponding to red ink chart)?

Answer 1

There are several ways to achieve this.

make-series operator allows to set default value for the periods where no data is present for aggregation:

customEvents
| where timestamp > ago(10m)
| make-series count() default=0 on timestamp in range(ago(10m), now(), 1m)
| render areachart

This will produce zero-filled data array and | render will build the chart accordingly.

If | summarize is preferred, you can create zero-filled range yourself with range operator:

let defaultValue = 0;
range timestamp from floor(ago(10m),1m) to floor(now() + 10m,1m) step 1m
| join kind=leftouter
(
    customEvents
    | where timestamp > floor(ago(10m),1m) and timestamp < floor(now(),1m)
    | summarize Value=count() by bin(timestamp, 1m)
) on timestamp
| project timestamp, value = iff(isnotempty(Value), Value, defaultValue)
| render areachart

Make sure to use join kind=leftouter to have all timestamps from the left side of the join present in output.