# Count only sum value of ID on their last date **URL:** https://discuss.elastic.co/t/count-only-sum-value-of-id-on-their-last-date/342227 **Category:** Kibana **Created:** [September 4, 2023, 8:04am UTC](https://discuss.elastic.co/t/count-only-sum-value-of-id-on-their-last-date/342227 "2023-09-04T08:04:56Z") **Posts on this page:** 8 **Page:** 1
### Author: ![Guillaume\_V](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/guillaume_v/32/124240_2.png) [@Guillaume\_V](https://discuss.elastic.co/u/Guillaume_V) #### Post date: [September 4, 2023, 8:04am UTC](https://discuss.elastic.co/t/count-only-sum-value-of-id-on-their-last-date/342227/1 "2023-09-04T08:04:56Z")
Hi, I have a problem i would like to share you. This is my data ![image](https://us1.discourse-cdn.com/elastic/original/3X/e/6/e690dd89941e99164861b258986abbec6a710754.png) And my table in Dashboard look like this : Level descending | Count 1 | 4 0 | 2 2 | 2 -1 | 1 The problem, it's for example, in level 2, he count 2 value. But (watch the first picture) ID 120555 is level 1 and more level 2 if we refere to its date. So for level 2 he must count 1 and not 2 value ! I know somes similars topic was created, but i haven't found any cases behaving like mine, or maybe I misunderstand the explanations ? Think you for your help ! ---
### Author: ![jsanz](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jsanz/32/53734_2.png) [@jsanz](https://discuss.elastic.co/u/jsanz) #### Post date: [September 5, 2023, 2:56pm UTC](https://discuss.elastic.co/t/count-only-sum-value-of-id-on-their-last-date/342227/2 "2023-09-05T14:56:56Z")
I don't think you can run that type of aggregation in Lens. You can aggregate by `id` and then show the latest value of `level` ordered by `timestamp` which is not exactly what you want. The only option I see is to use a [Latest Transform](https://www.elastic.co/guide/en/elasticsearch/reference/current/transform-overview.html#latest-transform-overview) to keep a separate index with just the latest document per `id` and visualize that. ---
### Author: ![Guillaume\_V](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/guillaume_v/32/124240_2.png) [@Guillaume\_V](https://discuss.elastic.co/u/Guillaume_V) #### Post date: [September 6, 2023, 3:29pm UTC](https://discuss.elastic.co/t/count-only-sum-value-of-id-on-their-last-date/342227/4 "2023-09-06T15:29:18Z")
I try your solution, and I think it's a good idea. Thank about it. I used "transform" as you said. But i have another problem. Documents in my new index get deleted after approximately 20 minutes. Did you have another idea ? ---
### Author: ![jsanz](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jsanz/32/53734_2.png) [@jsanz](https://discuss.elastic.co/u/jsanz) #### Post date: [September 6, 2023, 3:56pm UTC](https://discuss.elastic.co/t/count-only-sum-value-of-id-on-their-last-date/342227/5 "2023-09-06T15:56:38Z")
Transform have an optional retention policy. Maybe there is a default setting you need to override? (sorry I'm not an expert on this area) ![image](https://us1.discourse-cdn.com/elastic/original/3X/f/4/f486bee08c851ef140fb23e5fd8de8a84a17bd68.png) Documentation here: > **[Create transform API | Elasticsearch Guide \[8.9\] | Elastic](https://www.elastic.co/guide/en/elasticsearch/reference/current/put-transform.html)** ---
### Author: ![Guillaume\_V](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/guillaume_v/32/124240_2.png) [@Guillaume\_V](https://discuss.elastic.co/u/Guillaume_V) #### Post date: [September 7, 2023, 8:07am UTC](https://discuss.elastic.co/t/count-only-sum-value-of-id-on-their-last-date/342227/7 "2023-09-07T08:07:27Z")
> [@jsanz](#): > > ting you need to override? (sorry I'm not an expert on this area) I tried with several transforms by modifying the duration(100d / 7d / 1d). But it's the same result. Documents of new index pattern was deleted one by one after 15min. It's maybe because of another setting. But I'm having trouble seeing what ---
### Author: ![jsanz](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jsanz/32/53734_2.png) [@jsanz](https://discuss.elastic.co/u/jsanz) #### Post date: [September 7, 2023, 8:17am UTC](https://discuss.elastic.co/t/count-only-sum-value-of-id-on-their-last-date/342227/8 "2023-09-07T08:17:41Z")
You may want to open a new topic in the [Elasticsearch forum](https://discuss.elastic.co/c/elastic-stack/elasticsearch/6) that tackles that issue more precisely. ---
### Author: ![Guillaume\_V](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/guillaume_v/32/124240_2.png) [@Guillaume\_V](https://discuss.elastic.co/u/Guillaume_V) #### Post date: [September 7, 2023, 8:23am UTC](https://discuss.elastic.co/t/count-only-sum-value-of-id-on-their-last-date/342227/9 "2023-09-07T08:23:27Z")
I will do that. Thank you ---
### Author: ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system) #### Post date: [October 5, 2023, 8:24am UTC](https://discuss.elastic.co/t/count-only-sum-value-of-id-on-their-last-date/342227/10 "2023-10-05T08:24:22Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.