Kusto query to accumulate values in array

Question

I have a table in which values are stored as follows

I would like to add a column that contains an array with the values accumulated. For that purpose, I used the following function

.create-or-alter function calculateCumulativeSum(input: dynamic) {
 range i from 0 to array_length(input) - 1 step 1
| extend value = toint(input[i])
| extend cumulativeSum = row_cumsum(value)
| summarize make_list(cumulativeSum) }

This function works correctly

calculateCumulativeSum(dynamic([1,2,3,4])) gives the correct result [1,3,6,10]

But when I try to apply it to the table

inputTable | extend l = calculateCumulativeSum(fw) 

I get an error "Tabular expression is not expected in the current context"

I know it's possible to do it through mv-expand, row_cumsum, and summarize again - but is there a way to do it through a function ? I've tried to force the function to return a scalar (toscalar()), but then I get another error: can't use 'fw' as it is defined outside its row-context scope. (see: https://aka.ms/toscalar_limitations)

Answer 1

It seems possible using series_iir function

let rith_table = datatable(id:int, rith_col: dynamic)
[
    1, dynamic([1,2,3,4]),
    2, dynamic([4,8,12]),
    3, dynamic([7,14,21,28])
];
rith_table
| extend cum = series_iir(rith_col,dynamic([1]), dynamic([1,-1]))

This gives the expected result :

Answer 2

can't use 'fw' as it is defined outside its row-context scope.

As per Microsoft-Document:

toscalar() can't be applied on a scenario that applies the function on each row.

This should be used in row-context scope as this is a function limitation, which says a function can only be called on scalar value.

And even I received same error as below:

So, I suggest the same to use mv-expand as below:

let rith_table = datatable(id:int, rith_col: dynamic)
[
    1, dynamic([1,2,3,4]),
    2, dynamic([4,8,12]),
    3, dynamic([7,14,21,28])
];
rith_table
| mv-expand rith_col to typeof(int)
| serialize                  
| extend cmultve_col = row_cumsum(rith_col)       
| summarize cmultve_lst = make_list(cmultve_col) by id  
| join kind=inner (rith_table) on id         
| project id, rith_col, cmultve_lst

Output:

Fiddle.