Canvas and cumulative sum

[PaulSPG (Paul)]

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

[tims (Tim Schnell)]

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

[PaulSPG (Paul)]

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.

[PaulSPG (Paul)]

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'.