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

18

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