[Lens] Sum aggregation with missing values

[lizozom (Liza Katz)]

I have a lens visualization on top of this data set:

I want to show the total item quantity per date. On days where there was no data, I want to show the last value.

However, when I choose the sum aggregation and set Missing values to next, it still shows 0 for days where there was no data. (If I switch to a max aggregation for example, missing values do work!)

Note that on the 5th of January, the sum goes to 0, although there was no data for the 5th.

Is there a way to work around this?

[markov00 (Marco Vettorello)]

Hi @lizozom nice to see you here!

So by default ES return 0 when you apply a sum aggregation and there is no data in that bucket (is an opinionated math dilemma, every database/engine work differently). In this case you can enable Hide zero values in the advanced settings of the sum dimension (with that the final data doesn't contain value at zero).

Then if you want your visualization to "keep" the last value over these missing gaps you can use the Missing Value: Last. This should keep the line horizontal from the last available bucket until the bucket before the next available one (where the line will move again toward the next available value).

Let me know if this doesn't work in your case