Canvas and cumulative sum

Would it be possible to calculate the cumulative sum of a column using f.i. mapColumn. For example the price column of demodata.

Something like:
filters
| demodata
| mapColumn "CuSum Price" fn={math 'sum(price + CuSum[row-1]) '}
| table
| render

Hey @PaulSPG right now there is not a cumulative sum function in the expression language. You would have to do something like this using the Timelion data source in Canvas.

Here is a link to the Timelion cusum function: https://github.com/elastic/timelion/blob/master/FUNCTIONS.md#cusum

Thanks for the quick response. I already had a look at that one. But I haven't been able to get it working with more than 1 field. The documents retrieved from es have more than one field per row/doc. For example a table with columns: timestamp price1 price2. For the time being I will stick to the sum per time interval which already gives some insights.

Timelion is indeed the way to go. This script does what I need:

   filters
      | timelion 
        query="
        .es(index=*some_index*, metric=sum:Price1, timefield=@timestamp ).cusum().label('P1'),
        .es(index=*some_index*, metric=sum:Price2, timefield=@timestamp ).cusum().label('P2')"  interval="1M"
      | pointseries x="@timestamp" y="value" color="label"
      | plot defaultStyle={seriesStyle lines=2 } legend="ne"
      | render

The timelion query results in a table with 3 columns where value represents the cusum of Price1 or Price2 each month and label represents the 'origin' field (P1 for Price1 or P2 for Price2):

        @timestamp, value, label
        2019-10-01, 120, P1
        2019-11-01, 240, P1
        2019-10-01, 124, P2
        2019-11-01, 338, P2

The point series creates series P1 and P2 using the label column to 'split'.

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.