Sum aggregation Giving incorrect resumt

[Arshukla (Ashutosh)]

I'm encountering an issue in Kibana. When I check a particular variable in Discover, I can see that it has a value of 0 on four different dates. For the remaining dates, there is no data shown for this variable in Discover, indicating that no records were received for those days with respect to this variable.

However, when I visualize the same counter in Lens, it displays a dash ("–") for each date, whereas I expected it to show 0 for the four dates where data was actually received.**

When I disable the "Hide zero values" option, Lens shows 0 values for every date over the last 90 days — even though data was only received for four specific days.

Could you please help me resolve this issue?

[Marco_Liberati]

Hi @Arshukla

Discover and Lens are representing 2 things:

• Discover is showing the content of the documents in the index, so a 0 value is correctly represented as is
• Lens is performing aggregations over the data, so in your picture it is showing that there are no documents for those bucketed time slots. As a user you can decide whether to show 0 or -.

[Tortoise]

Hello @Arshukla

As per the screenshot you shared can you use "COUNT" instead of "SUM" as SUM of 0 will be 0 & "COUNT" of documents can give you document count > 0 by which hiding zero data will still show dates on which there is document present related to shutdown :

Thanks!!

[Arshukla (Ashutosh)]

Hi @Tortoise Please find the count of document. for other dates other than 4 days. I am seeing "-", which is correct, then why when using sum it is showing "0". For these 4 days it should show "0", for rest of the days it should show "-"............ When I am using avg instead of sum, I am able to see expected results. However I need sum of the counter
Count---

AVG ---

SUM -

@Marco_Liberati Yes I understood, that but you can se above screenshot, it should not be showing "-" for these 4 days

[Tortoise]

Hello,

From the original screenshot shared by you , field value of "pw.sum-Num-ESLS-Shutdown-Attempt" is 0
So i am assuming that it is trying to do SUM of value of 0 + 0 .. = 0 for 1 day
when we use COUNT it is showing SUM of documents not SUM of value of "pw.sum-Num-ESLS-Shutdown-Attempt" hence it shows numbers like 57/60/50/50

Coming back to your requirement , could you please share what you want to show. If you only want to show non-empty records, for the beginTimeperday column can disable include empty rows option

Thanks!!

[Arshukla (Ashutosh)]

Hi,

I’m trying to display the sum of all records for the field "pw.sum-Num-ESLS-Shutdown-Attempt" for each date. My requirement is as follows:

• For the four dates where data exists (with zero values), I want the sum to be shown as 0.
• For all other dates (where no data was received), I want the value to appear as a dash "–" (i.e., not 0).

However, when I attempt this in Lens, it currently shows a dash "–" for all dates, even for the ones where the sum should be 0.

I’ve already tried disabling the “Include empty rows” option, but it hasn’t resolved the issue.

Could you please assist?

[Tortoise]

Thanks for sharing the details. The requirement is clear.

Can you try below :

1. Use Last Value (which will show as 0 )
   For date field > Include empty rows > enabled
   For last value field > Hide zero values > enabled

Also i see using Avg also you have already achieved your requirement any specific reason you want to use only SUM?

Thanks!!

[Arshukla (Ashutosh)]

Hi
Please find the output, -
Last value doesn't have option of "Hide Zero values". (Screenshot attached for reference)

I need sum, as I need to apply formula when values are received. Average will not solve the purpose, once values will be received for this field except 0...

[Tortoise]

Hello,

I tried reviewing this again & have below findings :

DATE > Include empty rows > disabled
SUM > Hide zero values > disabled

This will not show the date when no data was received, now if your requirement is still you want to show that dates with - than i believe on others days you will not receive 0 in future as per your previous comment & it will be some value. If this hold true than below settings will work

DATE > Include empty rows > enabled
SUM > Hide zero values > enabled

In current scenario without having data under sum other than 0 & wanting to show both as per your requirement seems to be not possible.

Thanks!!

[Arshukla (Ashutosh)]

Thank you for your response.

I’d like to clarify the issue and the intended behavior a bit further:

• A value of 0 is a valid data point, meaning the variable was present in the logs, but its value was zero.
• A dash "–" should represent missing data, i.e., the field itself did not exist in the records for those dates.

If I display 0 for dates where the variable was not present at all, it would give the wrong impression—as if the variable existed and consistently had a value of zero, which is not true. This would mislead users into thinking the system was reporting but the value was just zero, rather than the fact that the field was completely absent.

On the other hand, if I show "–" even for the dates where the value was actually zero, that also misrepresents the data. In my case, I have four specific days where the variable was present and had a value of 0, and those should be clearly shown as 0, not as "–".

So ideally, I need:

• 0 for the four days where data was present and the value was zero.
• "–" for all other days where no data was received for this variable.

Currently, using the settings provided:

• "Include empty rows" = enabled
• "Hide zero values" = enabled

This causes all days to show "–", including the ones where the actual value is 0, which is incorrect for my use case.

On the other hand, if I disable those options, I get 0 for all dates, even the ones where the field wasn't present — which is also misleading.

Could you please suggest a way to achieve this distinction accurately? Displaying either 0 or "–" for all dates without differentiating between "zero value" and "no data" doesn't meet the reporting requirement.

Thanks again for your help!

[Tortoise]

Thanks @Arshukla for the detailed input.

First it is not clear why you want to sum when you are sending 0, what is the other value if it is not 0?

Why cant we use Count() which will give non-zero number & will differentiate the date when we received data with the date when there was no data.

Still if you are only going to send 0 always and not any other value than Last value for me is working as per the requirement you want :

Thanks!!

[RainTown (Kevin Maguire)]

@Tortoise

First it is not clear why you want to sum when you are sending 0, what is the other value if it is not 0?

I wondered that too. But I'm presuming he's expecting a non-zero number might appear at some point in the future.

This is one of those Qs where @Arshukla likely knows what he's trying to do/show, but is sharing so little contextual information, just fixated on the "0" vs "-" aspect, that its hard to help.

Personally I think the sum of zero values is zero, since a sum is a number. But then I studied mathematics, and we work with sets with zero elements (∅) all the time.

The number of things summed can also be zero.

If use case means I need to know both, the sum and the number of things summed, then I'd just show both.

[Arshukla (Ashutosh)]

Hi Team,

Thanks for your responses and the helpful feedback — I appreciate the ongoing discussion.

To clarify my use case more precisely:

I’m working with a field (XYZ) that can have a mix of zero, non-zero, and missing values over time. I'm using a sum aggregation because this field can — and eventually will — include multiple values per day that need to be totaled for reporting. The use of last value would not be appropriate in this scenario because it would ignore the rest of the data points for a given day.

Here’s a concrete example of what the data looks like:

• **May 1st to May 5th: We received data for the field, and the value was 0 each day.
• **May 6th to May 9th: No data was received at all for the field — it was completely absent from the logs.
• **May 10th to May 15th: We received multiple records per day, and the daily sums were 24, 47, 57, 89, 53, and 63, respectively.

Current Behavior in Lens

When I visualize this using a daily sum aggregation:

• From May 1–5 (where the actual sum is 0), Lens shows either a dash "–" or 0 depending on the "Hide zero values" setting.
• From May 6–9 (where no data was received), Lens shows the same output — a dash or 0 — making it impossible to differentiate from the valid zero values.
• From May 10–15, the summed values display correctly.

Why This Is a Problem

The current behavior treats zero values and missing data identically, which leads to incorrect interpretation:

• A value of 0 means the system reported data and the result was zero — this is meaningful and should be displayed explicitly.
• A dash "–" means no data was received — the field was entirely missing — which is a different and equally important signal.

By showing 0 for missing data, it implies that the system was active but nothing was recorded, which is not true. Similarly, showing a dash for actual 0 values hides meaningful data and misleads users.

What I'm Looking For

Ideally, I need the visualization to:

• Show 0 for dates when the field was present but had zero values.
• Show "–" for dates when no data was received (field was missing).
• Show the correct sum for dates when data was present and non-zero.

I understand this may not be achievable through default Lens options alone, but if there is any workaround — such as combining sum with count, using a scripted field, or TSVB — I’d be grateful for any guidance.

Thanks again for your time and support!

[Tortoise]

Thanks @Arshukla for the details.

As per my understanding the requirement you have will be difficult to implement as Kibana cannot differentiate between no records (0) & sum of records with value (0) in order to show the value as 0 & - respectively.

I believe below will be the best solution as per me if you still want to implement this :

I have used formula :
ifelse(count(id)>0,ifelse(sum(value) == 0,0,sum(value)),-1)

You can edit the formula as per your fields.

When sum is 0 , it will show 0
When sum is anything is else , it will show that value of sum example 2
When record is not present , it will show -1

Thanks!!

[RainTown (Kevin Maguire)]

@Arshukla

The current behavior treats zero values and missing data identically, which leads to incorrect interpretation:

I don't concur with that characterization. Any interpretation, correct or otherwise, is at least partially subjective. The sum of zero values IS zero, as I mentioned before. Whats the sum of the coins in your pocket? If you have no coins in your pocket, zero is (IMHO) an accurate answer.

When you sum numeric data you have to lose information, 4 == 1+3 == 2+1+1 == 2 + (-2) == ... .

I'd say 100 zeroes added together are the same as zero zeroes added together. If you want/need to know how many terms were added to reach a sum given, you need to show that too.

More accurate is that there are Kibana UI options which allow some flexibility between showing zero as zero, or as "-", which is the same as the UI uses for missing values in many scenarios. You could raise an enhancement request for this, just ask to use a different symbol (maybe ∅) for missing values, but as that's a pretty established option/convention in the kibana UI I'd expect it to be rejected.

Below is some data matching what you described which I used for testing.

I would visualize this in a old-style table as follows:

or in Lens via:

Personally, and though I appreciate @Tortoise was just trying to meet your requirements, I would much rather not show "-1" for a sum when "-1" is NOT the actual sum. That sort of mental jump, an invented convention, might appear simple today, down the line when everyone involved has moved on, good chance it's just confusing. YMMV.

POST _bulk
{ "index" : { "_index" : "zerotest", "_id" : "1" } }
{ "@timestamp": "2025-05-01T12:00:00Z",  "field1" : 0 }
{ "index" : { "_index" : "zerotest", "_id" : "2" } }
{ "@timestamp": "2025-05-02T12:00:00Z",  "field1" : 0 }
{ "index" : { "_index" : "zerotest", "_id" : "3" } }
{ "@timestamp": "2025-05-03T12:00:00Z",  "field1" : 0 }
{ "index" : { "_index" : "zerotest", "_id" : "4" } }
{ "@timestamp": "2025-05-04T12:00:00Z",  "field1" : 0 }
{ "index" : { "_index" : "zerotest", "_id" : "5" } }
{ "@timestamp": "2025-05-05T12:00:00Z",  "field1" : 0 }
{ "index" : { "_index" : "zerotest", "_id" : "10a" } }
{ "@timestamp": "2025-05-10T12:00:00Z",  "field1" : 20 }
{ "index" : { "_index" : "zerotest", "_id" : "10b" } }
{ "@timestamp": "2025-05-10T13:00:00Z",  "field1" : 4 }
{ "index" : { "_index" : "zerotest", "_id" : "11a" } }
{ "@timestamp": "2025-05-11T12:00:00Z",  "field1" : 40 }
{ "index" : { "_index" : "zerotest", "_id" : "11b" } }
{ "@timestamp": "2025-05-11T13:00:00Z",  "field1" : 7 }
{ "index" : { "_index" : "zerotest", "_id" : "12a" } }
{ "@timestamp": "2025-05-12T12:00:00Z",  "field1" : 50 }
{ "index" : { "_index" : "zerotest", "_id" : "12b" } }
{ "@timestamp": "2025-05-12T13:00:00Z",  "field1" : 7 }
{ "index" : { "_index" : "zerotest", "_id" : "13a" } }
{ "@timestamp": "2025-05-13T12:00:00Z",  "field1" : 80 }
{ "index" : { "_index" : "zerotest", "_id" : "13b" } }
{ "@timestamp": "2025-05-13T13:00:00Z",  "field1" : 9 }
{ "index" : { "_index" : "zerotest", "_id" : "14a" } }
{ "@timestamp": "2025-05-14T12:00:00Z",  "field1" : 40 }
{ "index" : { "_index" : "zerotest", "_id" : "14b" } }
{ "@timestamp": "2025-05-14T13:00:00Z",  "field1" : 10 }
{ "index" : { "_index" : "zerotest", "_id" : "14c" } }
{ "@timestamp": "2025-05-14T13:00:00Z",  "field1" : 3 }
{ "index" : { "_index" : "zerotest", "_id" : "15a" } }
{ "@timestamp": "2025-05-15T12:00:00Z",  "field1" : 60 }
{ "index" : { "_index" : "zerotest", "_id" : "15b" } }
{ "@timestamp": "2025-05-15T13:00:00Z",  "field1" : 1 }
{ "index" : { "_index" : "zerotest", "_id" : "15c" } }
{ "@timestamp": "2025-05-15T14:00:00Z",  "field1" : 1 }
{ "index" : { "_index" : "zerotest", "_id" : "15d" } }
{ "@timestamp": "2025-05-15T15:00:00Z",  "field1" : 1 }