kqlazure-data-explorerazure-log-analyticskusto-explorer

How to get data for specific date (startofweek) in a given time range in KQL?


Sorry if this question was already asked, couldn't find any related thread hence posting. I want to display count of sales data of start of every week (Sunday). The following sample query displays count of sales for start of a week 30 days ago. What is the best way to display, same data for start of every week, in the given time range i.e.. past 30 days. (it's not aggregation of entire week data but just trying to get the count of pending sales on the day of start of every week in the time range shown below). Thanks in advance.

SalesData
| where DateStamp >= startofweek(ago(30d))
| project DateStamp, Sales
| summmarize Sales=Count(), PendingSales=countif(Sales == "Pending") by Week=startofweek(ago(30d))

Output:

Week | Sales | PendingSales

2024-06-30 00:00:00.0000000 | 45 | 23

Trying to get similar output as above for following time range:

range Week from startofweek(ago(30d)) to now() step 7d

Week

2024-06-30 00:00:00.0000000

2024-07-07 00:00:00.0000000

2024-07-14 00:00:00.0000000

2024-07-21 00:00:00.0000000

2024-07-28 00:00:00.0000000

2024-08-04 00:00:00.0000000


Solution

  • You can get desired output using below KQL Query:

    let ri_ed = startofweek(now()); 
    let ri_sd = startofweek(ago(30d));
    range Rith from ri_sd to ri_ed step 7d
    | join kind=leftouter (
        SalesData
        | extend Rith = startofweek(DateStamp)
        | summarize Rith_sales = count(), PendingSales = countif(RSales == "Pending") by Rith
    ) on Rith
    | project Rith, Sales = coalesce(Rith_sales, 0), PendingSales = coalesce(PendingSales, 0)
    | order by Rith asc
    

    Output:

    enter image description here

    Fiddle.