Count only sum value of ID on their last date

I have a problem i would like to share you.
This is my data

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 !

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 to keep a separate index with just the latest document per id and visualize that.

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 ?

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)


Documentation here:

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

You may want to open a new topic in the Elasticsearch forum that tackles that issue more precisely.

1 Like

I will do that. Thank you

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.