KQL draw a few average values with different periods in a single graph

user_13131510 0
Viewed 79

Is it possible to draw a few average values with different periods in a single graph?

I have something like the blue graph. I need to add something like black and red graphs.

enter image description here

My sample dataset is:

let dt = datatable (dt:datetime, val:int)
[
     '2022-04-18T03:20:39.956Z' ,3
    ,'2022-04-18T03:55:22.471Z' ,-1
    ,'2022-04-18T04:02:40.761Z' ,-4
    ,'2022-04-18T04:10:41.68Z'  ,-5
    ,'2022-04-18T04:21:19.707Z' ,-1
    ,'2022-04-18T04:31:53.644Z' ,-3
    ,'2022-04-18T04:33:40.543Z' ,1
    ,'2022-04-18T04:33:58.73Z'  ,2
    ,'2022-04-18T04:41:18.17Z'  ,-11
    ,'2022-04-18T04:47:33.845Z' ,1
    ,'2022-04-18T05:04:32.776Z' ,1
    ,'2022-04-18T05:04:22.776Z' ,0
    ,'2022-04-18T06:20:39.956Z' ,3
    ,'2022-04-18T06:55:22.471Z' ,-1
    ,'2022-04-18T07:02:40.761Z' ,-4
    ,'2022-04-18T07:10:41.68Z'  ,-5
    ,'2022-04-18T07:21:19.707Z' ,-1
    ,'2022-04-18T07:31:53.644Z' ,-3
    ,'2022-04-18T07:33:40.543Z' ,1
    ,'2022-04-18T07:33:58.73Z'  ,2
    ,'2022-04-18T07:41:18.17Z'  ,-11
    ,'2022-04-18T07:47:33.845Z' ,1
    ,'2022-04-18T08:04:22.776Z' ,0
    ,'2022-04-18T08:04:32.776Z' ,4
    ,'2022-04-18T09:20:39.956Z' ,3
    ,'2022-04-18T09:55:22.471Z' ,-1
    ,'2022-04-18T10:02:40.761Z' ,-4
    ,'2022-04-18T10:10:41.68Z'  ,-5
    ,'2022-04-18T10:21:19.707Z' ,-1
    ,'2022-04-18T10:31:53.644Z' ,-3
    ,'2022-04-18T10:33:40.543Z' ,1
    ,'2022-04-18T10:33:58.73Z'  ,2
    ,'2022-04-18T10:41:18.17Z'  ,-11
    ,'2022-04-18T10:47:33.845Z' ,1
    ,'2022-04-18T11:04:12.776Z' ,0
    ,'2022-04-18T11:04:22.776Z' ,4
    ,'2022-04-18T11:04:32.776Z' ,-5
    ,'2022-04-18T12:20:39.956Z' ,3
    ,'2022-04-18T12:55:22.471Z' ,-1
    ,'2022-04-18T13:02:40.761Z' ,-4
    ,'2022-04-18T13:10:41.68Z'  ,-5
    ,'2022-04-18T13:21:19.707Z' ,-1
    ,'2022-04-18T13:31:53.644Z' ,-3
    ,'2022-04-18T13:33:40.543Z' ,1
    ,'2022-04-18T13:33:58.73Z'  ,2
    ,'2022-04-18T13:41:18.17Z'  ,-11
    ,'2022-04-18T13:47:33.845Z' ,1
    ,'2022-04-18T14:04:22.776Z' ,0
];
dt 
| make-series avg(val), min(val), max(val) on dt step 1h
| render timechart

It looks like sample dataset is to big and I have to add some text here )

kql graph
1 Answers

Yes, it is possible.
Also check the generation of the data sample.

// Generation of a data sample. Not part of the solution.
let t = materialize(range i from 1 to 300 step 1 | project dt = ago(365d*50*rand()), val = -100 + toint(200*rand()));
// The solution starts here.
union
     (t | summarize avg(val) by dt = startofmonth(dt)                      | extend type = "Monthly")
    ,(t | summarize avg(val) by dt = startofyear(dt)                       | extend type = "Annual")
    ,(t | summarize avg(val) by dt = make_datetime(bin(getyear(dt),5),1,1) | extend type = "5 Year")
| render timechart

Avg

Fiddle

user_63364790
Related