azure - Aggregate data per Bank and per day in KQL

Question

I'm in working on project with goal of connecting multiple banks, in Netherlands, into our platform.

For now, every time a user connects to a single bank, we want to send out a metric and show it in Azure dashboard. We are almost there, except that we want to aggregate the sum per day. This is what we have right now:

For example, looking at ABN AMRO, we have:

1. ABN AMRO had 2081 connections on 25/01/2021
2. ABN AMRO had 2325 connections on 24/01/2021
3. ABN AMRO had 5082 connections on 23/31/2021

But what we want is to sum it like this:

1. ABN AMRO had 2081 + 2325 + 5082 on 25/01/2021 = 9488
2. ABN AMRO had 2325 + 5082 on 24/01/2021 = 7407
3. ABN AMRO had 5082 on 23/31/2021 = 5082

This is the query used so far:

customMetrics
| where name == "CustomerGrantedConsent"
| extend BankName = customDimensions.BankName
| summarize Count = count() by tostring(BankName), bin(timestamp, 1d)
| order by timestamp

How?

question from:https://stackoverflow.com/questions/65882398/aggregate-data-per-bank-and-per-day-in-kql

Answer 1

Try using row_cumsum

customMetrics
| where name == "CustomerGrantedConsent"
| extend BankName = customDimensions.BankName
| summarize Count = count() by tostring(BankName), bin(timestamp, 1d)
| order by timestamp
| serialize
| extend cumsum = row_cumsum(Count, BankName != prev(BankName))

It will return the output as your require

1. ABN AMRO had 2081 + 2325 + 5082 on 25/01/2021 = 9488
2. ABN AMRO had 2325 + 5082 on 24/01/2021 = 7407
3. ABN AMRO had 5082 on 23/31/2021 = 5082

You can read about the row_cusmum at here.