Kusto | Get average counts by quarter and display line chart with dates (include quarters with no data)

0
votes

I am trying to find the best way (or any way) to create a line chart to display the average count of something per quarter. When I say quarterly I basically mean by 91 day increments (not calendar quarters such as 01/01 - 03/01). I want to output this to a line chart to show the trend, so I also want the chart to show 0 counts where there is no data for a quarter.

To show an example, lets say I have the following table of attendance dates for students:

let SampleTable = datatable(AttendanceDate:datetime, Student:string) 
[
    "02-01-2021", "John",
    "01-01-2021", "Jane",
    "06-01-2020", "Bill",
];

And I want to show a chart of attendance by quarter, so I try something like this:

SampleTable
| summarize AttendanceCount = count() by Quarter = datepart("Quarter", AttendanceDate)
| order by Quarter desc
| render linechart

And I get this actual output

Which is not quite what I'm going for. In this example the quarters would run like this (just for reference)

Q1 = 02/01/2021 - 12/01/2020
Q2 = 11/31/2020 - 09/01/2020
Q3 = 08/31/2020 - 06/01/2020

So ideally the output would look like this: ideal output

I don't know if this is possible without writing a long/complex query, but anything close to this (for instance displaying a single date instead of a range) would be a big help.

1
azure-data-explorerkql

1 Answers

2
votes

here's an option:

let SampleTable = datatable(AttendanceDate:datetime, Student:string) 
[
    "02-01-2021", "John",
    "01-01-2021", "Jane",
    "06-01-2020", "Bill",
];
let start = toscalar(SampleTable | summarize min(AttendanceDate));
SampleTable
| make-series count() on AttendanceDate from start to now() step 91d
| render timechart

result as timechart