CODEWITHSUNDEEP
azureazure-devopsazure-functionskql
user4254528
user4254528

Reputation:

Aggregate data per Bank and per day in KQL

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:

connections last 3 days

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?

Upvotes: 0

Views: 336

Answers (2)

user1672994
user1672994

Reputation: 10849

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.

Upvotes: 0

silent
silent

Reputation: 16208

let T = datatable(day:datetime  , value:long)
[
   "2021-01-25", 3000,
   "2021-01-24", 2000,
   "2021-01-23", 1000
];
T
| order by day asc 
| serialize cs=row_cumsum(value)
| project  day, cs

enter image description here

Upvotes: 0

Related Questions