Max between two fields in last X days Kibana(lens)

Elastic Stack Kibana
1

Hello,

I am stuck at a problem, where i have to get the maximum between two fields in a document in last X days. I am using the table format with lens.
For example document has:

name=test
description=test description
a=12
b=13

name=test
description=test description
a=14
b=15

and there can be many such documents that will have the same variable with different value over a period of x days.

My objective is to get the max(a,b) over a period of X days.

Could someone provide any solutions to this problem.

Thanks

2

You can add a new field 'ab' and add {'copy_to': 'ab'} to the mapping parameter of both a and b fields.
Then you can use max aggregatation on 'ab' field with Date range aggregation about X days.

3

@Tomo_M okay thanks. So if i do a max aggregation on those field, i get the errors on normal rows section where it is asking to use filter. If i use filter for the normal rows it takes only predefined ones the new ones wont show up in the table.

4

Sorry I missed you're using lens.

What did you do and what happened? Can you share screenshots or error messages?

You can use max aggregation by "Metrics > Maximum" and filter last X days by the timepicker in the right-top.

If you correctly set {"copy_to": "AB"}, it will work.

image
image1912×1196 99 KB

5

Hi @dash5000

welcome to the Kibana community.

Another possible approach would be to have a "creative" usage of the Formula feature in Lens, as I describe in this post. In your case the metric(fieldA) would be max(fieldA), and same apply for metric(fieldB) => max(fieldB).
Combine this formula with a date histogram should work as you describe.

6

@Tomo_M so the new field 'ab' should i add it in the mapping itself with type text ?

7

Hello @Marco_Liberati ,

I tried this . Is this the right way if i want to get the maximum in the past 1 month from today's date.
clamp(max('a', shift='1M') - max('b', shift='1M'), 0, 1) * max('a', shift='1M') + clamp(max('b', shift='1M') - max('a', shift='1M'), 0, 1) * max('b', shift='1M')
?

8

@dash5000
Please see here.

@Marco_Liberati
The clamp trick is really amazing and I didn't even think of that. Thank you for sharing the information.

9

Thanks @Tomo_M , but if i copy it will be a single value right. for example
a=12, b=12
copy to ab would be 12 12, so if i take the max then i guess it will be the same.

10

Yes, ab would be [12, 12] and max(ab) would be 12 for that document. Isn't it what you want? What is desired output?

11

Yes that looks correct to me.

12

@Tomo_M Yup. I was checking the link that you provided since it saw that it was text so thought that it might not be a list. But if its a list then this will work too . Thanks will try this approach too.

One more thing so if i understand correctly the shift that we use is like if i use 1M so the maximum will be taken before one month of data
For example:
today date: 2022-01-19
If i do it 1M in shift,
it would take maximum from the beginning to 2021-12-19 not from (2021-12-19 to 2022-01-19) right?

13

Shift will translate the current bucket by the given amount.
Say today is 2022-01-19, you have a date histogram where each bucket is 1 day, then shifting by 1M means taking the aggregated value of 2021-12-19 .

14

@Marco_Liberati thanks for clarifying.
So, if i want to do take the maximum from now till last 1M.
like from 2022-01-19 to 2021-12-19, is there anything like range that i can add in the max ?

15

You could define a big time range, like one year in the top bar, then define a date histogram in Lens and use a custom interval of 1 month.
At this point you know that the metric max(fieldA) will contain the maximum of each month for the fieldA.

16

Ya will try this out. Thanks

17

@Marco_Liberati , is there any other way than to change to big time range at the top bar and adding a custom interval of 1 month, because the same table contains other metrics which are max of the some field for the complete data not just 1 month. I guess that metrics will also be calculated only for one month right or it will do the normal max on the whole data