Accamulated data in pivot mode

user_10717265 0
Viewed 52

Now i accamulate columns via row_cumsum

test
| project Boenheter, Ar, Maned, ManedTLA 
| extend _date = make_datetime(toint(Ar), Maned, 1) 
| extend key1 = Ar, __auto0 = datetime_part('Month', startofmonth(_date)) 
| summarize value0 = sum(Boenheter) by key1, __auto0, ManedTLA 
| order by __auto0 asc, key1 asc 
| serialize value0 = **row_cumsum(value0, __auto0 != prev(__auto0))** 
| extend __p = pack(tostring(ManedTLA), value0) 
| summarize __p = make_bag(__p) by key1 
| evaluate bag_unpack(__p) 
| order by key1 asc

enter image description here

But i wanna do accamulation for rows in next approach: Feb = Jan + Feb, Mar = Jan + Feb + Mar, etc... so Feb = 304, Mar = 624 (for 2012 year as example) and so on

enter image description here

Does Kusto have some hack for do accamulation for row instead columns (row_cumsum)?

Help please)

kql azure-data-explorer
1 Answers

Use row_cumsum, with restart on year change, before using pivot

// Generation of a data sample. No part of the solution.
let t = materialize(range i from 1 to 200 step 1 | extend dt = ago(365d*10*rand()));
// The solution starts here.
t
| summarize count() by year = getyear(dt), month = format_datetime(dt,'MM')
| order by year asc, month asc
| extend cumsum = row_cumsum(count_, year != prev(year))
| evaluate pivot(month, any(cumsum), year)
year 01 02 03 04 05 06 07 08 09 10 11 12
2012 2 4 6 7 10 14 16
2013 2 3 7 8 10 11 15 16 17 18
2014 2 7 11 12 13 14 15 17 19 20
2015 2 3 6 10 11 12 13 14 15
2016 1 2 3 5 6 8 10 11 12 15 16 19
2017 1 2 5 8 13 16 17 20 21
2018 4 5 8 12 15 18 20 23 24 25 26
2019 5 7 8 10 11 14 18 19 20 21
2020 2 5 8 10 11 13 15 16 19 22
2021 2 5 6 7 8 9 11 17
2022 2 4 5

Fiddle

user_63364790
Related