Bar chart based on cumulative function

[wadhah]

Hello all,

I have an index "consump" having as fields : category, timestamp, costs.
I want to display in Kibana Canvas, a bar chart exhibiting cumulative sum(costs).
In other cases I used "timelion" for that like this: "

timelion
query=".es(index=consumption*, metric=sum:COSTS, timefield=@timestamp).cusum()" interval="1d" from="now-1M" to="now"

"

The problem in this case is that calculating the sum(costs) is not straight forward : for example if I need to calculate the SUM(costs) of the current month I need to do such query(since it is a blended cost):

essql
query="SELECT MAX(costs)
AS costs
FROM "consump*"
WHERE MONTH("@timestamp")=MONTH(CURRENT_DATE())
GROUP BY "category""
| math "round(sum(costs),2)"

So, here in this case the sum of the costs will be equal to 5+6+2=13

I want to build a bar chart where : y_axis=cummulative_sum(costs) and x_axis=timestamp

So, can you please help me on how to proceed with such case.

[bhavyarm]

@tims @Catherine_Liu can we get some help? Thanks!

[wadhah]

Thanks @bhavyarm for your quick interaction

[tims (Tim Schnell)]

Hi @wadhah, I'm not sure I'm completely following your example but have you tried using mapColumn to calculate your cumulative sum?

[wadhah]

Hey @tims : Thank you for your response.
Actually, I gave a wrong example.
Please find below the right one:

So, basically here, for example, for the category "c" : between the 1st and the 15th day, it hasn't consumed money as the costs remained at 1$. However, between the 15th and the 20th day it has consumed 1$. Same for "a" : between 1st and 15th day, it consumed 1$ and between 15th and 20th day, it consumed 4$.
I hope this makes the picture clear.
I tried to use mapColumn, but it did not work for me.