# Keeping only latest "Date Histogram" aggregation in a datatable

**URL:** https://discuss.elastic.co/t/keeping-only-latest-date-histogram-aggregation-in-a-datatable/117842
**Category:** Kibana
**Created:** [January 31, 2018, 3:10pm UTC](https://discuss.elastic.co/t/keeping-only-latest-date-histogram-aggregation-in-a-datatable/117842 "2018-01-31T15:10:46Z")
**Posts on this page:** 1
**Showing post:** 5

<div class="post-metadata">

### Author: ![anthony-o](https://avatars.discourse-cdn.com/v4/letter/a/d07c76/32.png) [@anthony-o](https://discuss.elastic.co/u/anthony-o)
#### Post date: [February 9, 2018, 10:22am UTC](https://discuss.elastic.co/t/keeping-only-latest-date-histogram-aggregation-in-a-datatable/117842/5 "2018-02-09T10:22:29Z")

</div>

> [@Joe\_Fleming](#):
>
> I don't understand what you mean by that. Even if the data is coming in after the fact, seeng the historical data seems like it would smooth over that problem. I probably just don't fully understand your data and what you're trying to do with it.

Actually, I've asked [another question](https://discuss.elastic.co/t/split-and-use-the-term-in-sub-operation-in-timelion/117083) to compute a % ratio between the `valid` data and `error` ones and first wanted to display it in a Datatable before seeing in that forum that this was impossible (to create a column with computed information).  
So I've created this graph and as you can see in the following monthly graph (aggregated on days), I don't have data every days:  
 ![image](https://us1.discourse-cdn.com/elastic/original/3X/3/d/3d7779f182249b4d1f8e91ea7bcb2ea5d0b9111d.png)

So for the moment, the workaround I've found in order to have the values displayed in a table, is to do the following Data configuration:

- Aggregation: Sum
- Field: times

_Buckets:_

- Split Rows
  - Aggregation: Terms
  - Field: from\_host.keyword
  - Order By: Term
  - Order: Descending / Size: 30
  - Custom Label: From Host

- Split Table (Rows)
  - Aggregation: Date Histogram
  - Field: @timestamp
  - Interval: Daily
  - JSON Input: {"order": {"\_key": "desc"}}
  - Custom Label: Day

- Split Table (Columns)
  - Sub Aggregation: Filters
  - Filter 1 - Valids: type.keyword:valid
  - Filter 2 - Errors: type.keyword:error
  - Order: Descending / Size: 5

I now have the following table:

```
+-----------------------------------------------------+
| 2018-02-07: Day |
+--------------------------+--------------------------+
| Valids: filters | Errors: filters |
+-----------+--------------+-----------+--------------+
| From Host | Sum of times | From Host | Sum of times |
+-----------+--------------+-----------+--------------+
| host1 | 54 | host1 | 1 |
| host2 | 72 | host1 | 8 |
| ... | ... | ... | ... |
+-----------+--------------+-----------+--------------+
| 2018-02-06: Day |
+--------------------------+--------------------------+
| Valids: filters | Errors: filters |
+-----------+--------------+-----------+--------------+
| From Host | Sum of times | From Host | Sum of times |
+-----------+--------------+-----------+--------------+
| ... | ... | ... | ... |

```

My ideal would be to simply display a % ratio between valids and errors and only display the data for the latest day for which I have data... it seems impossible for the ratio, but perhaps it is for the "keep only latest day for which we have data" requirement?

---

_[View the full topic](https://discuss.elastic.co/t/keeping-only-latest-date-histogram-aggregation-in-a-datatable/117842)._