# Max between two fields in last X days Kibana(lens) **URL:** \ **Category:** Kibana\ **Created:** [January 19, 2022, 5:20am UTC](https://discuss.elastic.co/t/max-between-two-fields-in-last-x-days-kibana-lens/294765 "2022-01-19T05:20:10Z")\ **Posts on this page:** 18\ **Page:** 1
**Author:** ![dash5000](https://avatars.discourse-cdn.com/v4/letter/d/c6cbf5/32.png) [@dash5000](https://discuss.elastic.co/u/dash5000)\ **Post date:** [January 19, 2022, 5:20am UTC](https://discuss.elastic.co/t/max-between-two-fields-in-last-x-days-kibana-lens/294765/1 "2022-01-19T05:20:10Z")
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 ---
**Author:** ![Tomo\_M](https://avatars.discourse-cdn.com/v4/letter/t/848f3c/32.png) [@Tomo\_M](https://discuss.elastic.co/u/Tomo_M)\ **Post date:** [January 19, 2022, 8:18am UTC](https://discuss.elastic.co/t/max-between-two-fields-in-last-x-days-kibana-lens/294765/2 "2022-01-19T08:18:01Z")
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. ---
**Author:** ![dash5000](https://avatars.discourse-cdn.com/v4/letter/d/c6cbf5/32.png) [@dash5000](https://discuss.elastic.co/u/dash5000)\ **Post date:** [January 19, 2022, 2:47pm UTC](https://discuss.elastic.co/t/max-between-two-fields-in-last-x-days-kibana-lens/294765/3 "2022-01-19T14:47:52Z")
@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. ---
**Author:** ![Tomo\_M](https://avatars.discourse-cdn.com/v4/letter/t/848f3c/32.png) [@Tomo\_M](https://discuss.elastic.co/u/Tomo_M)\ **Post date:** [January 19, 2022, 2:57pm UTC](https://discuss.elastic.co/t/max-between-two-fields-in-last-x-days-kibana-lens/294765/4 "2022-01-19T14:57:04Z")
> [@dash5000](#): > > I am using the table format with lens. Sorry I missed you're using lens. > [@dash5000](#): > > 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. 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](https://us1.discourse-cdn.com/elastic/original/3X/3/e/3ed085e1e5776450f21458d054e42da07294f587.png) ---
**Author:** ![Marco\_Liberati](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/marco_liberati/32/82953_2.png) [@Marco\_Liberati](https://discuss.elastic.co/u/Marco_Liberati)\ **Post date:** [January 19, 2022, 2:59pm UTC](https://discuss.elastic.co/t/max-between-two-fields-in-last-x-days-kibana-lens/294765/5 "2022-01-19T14:59:16Z")
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](https://discuss.elastic.co/t/dec-4th-2021-en-advanced-trick-and-tips-in-lens/289935#formula-comparisons-6). 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. ---
**Author:** ![dash5000](https://avatars.discourse-cdn.com/v4/letter/d/c6cbf5/32.png) [@dash5000](https://discuss.elastic.co/u/dash5000)\ **Post date:** [January 19, 2022, 3:20pm UTC](https://discuss.elastic.co/t/max-between-two-fields-in-last-x-days-kibana-lens/294765/6 "2022-01-19T15:20:00Z")
@Tomo_M so the new field 'ab' should i add it in the mapping itself with type text ? ---
**Author:** ![dash5000](https://avatars.discourse-cdn.com/v4/letter/d/c6cbf5/32.png) [@dash5000](https://discuss.elastic.co/u/dash5000)\ **Post date:** [January 19, 2022, 3:30pm UTC](https://discuss.elastic.co/t/max-between-two-fields-in-last-x-days-kibana-lens/294765/7 "2022-01-19T15:30:51Z")
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')` ? ---
**Author:** ![Tomo\_M](https://avatars.discourse-cdn.com/v4/letter/t/848f3c/32.png) [@Tomo\_M](https://discuss.elastic.co/u/Tomo_M)\ **Post date:** [January 19, 2022, 3:31pm UTC](https://discuss.elastic.co/t/max-between-two-fields-in-last-x-days-kibana-lens/294765/8 "2022-01-19T15:31:48Z")
@dash5000 Please see [here](https://www.elastic.co/guide/en/elasticsearch/reference/current/copy-to.html). @Marco_Liberati The clamp trick is really amazing and I didn't even think of that. Thank you for sharing the information. ---
**Author:** ![dash5000](https://avatars.discourse-cdn.com/v4/letter/d/c6cbf5/32.png) [@dash5000](https://discuss.elastic.co/u/dash5000)\ **Post date:** [January 19, 2022, 3:39pm UTC](https://discuss.elastic.co/t/max-between-two-fields-in-last-x-days-kibana-lens/294765/9 "2022-01-19T15:39:38Z")
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. ---
**Author:** ![Tomo\_M](https://avatars.discourse-cdn.com/v4/letter/t/848f3c/32.png) [@Tomo\_M](https://discuss.elastic.co/u/Tomo_M)\ **Post date:** [January 19, 2022, 3:42pm UTC](https://discuss.elastic.co/t/max-between-two-fields-in-last-x-days-kibana-lens/294765/10 "2022-01-19T15:42:21Z")
> [@dash5000](#): > > 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. 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? ---
**Author:** ![Marco\_Liberati](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/marco_liberati/32/82953_2.png) [@Marco\_Liberati](https://discuss.elastic.co/u/Marco_Liberati)\ **Post date:** [January 19, 2022, 3:47pm UTC](https://discuss.elastic.co/t/max-between-two-fields-in-last-x-days-kibana-lens/294765/11 "2022-01-19T15:47:10Z")
Yes that looks correct to me. ---
**Author:** ![dash5000](https://avatars.discourse-cdn.com/v4/letter/d/c6cbf5/32.png) [@dash5000](https://discuss.elastic.co/u/dash5000)\ **Post date:** [January 19, 2022, 3:56pm UTC](https://discuss.elastic.co/t/max-between-two-fields-in-last-x-days-kibana-lens/294765/12 "2022-01-19T15:56:13Z")
@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? ---
**Author:** ![Marco\_Liberati](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/marco_liberati/32/82953_2.png) [@Marco\_Liberati](https://discuss.elastic.co/u/Marco_Liberati)\ **Post date:** [January 19, 2022, 4:28pm UTC](https://discuss.elastic.co/t/max-between-two-fields-in-last-x-days-kibana-lens/294765/13 "2022-01-19T16:28:43Z")
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 . ---
**Author:** ![dash5000](https://avatars.discourse-cdn.com/v4/letter/d/c6cbf5/32.png) [@dash5000](https://discuss.elastic.co/u/dash5000)\ **Post date:** [January 19, 2022, 4:30pm UTC](https://discuss.elastic.co/t/max-between-two-fields-in-last-x-days-kibana-lens/294765/14 "2022-01-19T16:30:19Z")
@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 ? ---
**Author:** ![Marco\_Liberati](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/marco_liberati/32/82953_2.png) [@Marco\_Liberati](https://discuss.elastic.co/u/Marco_Liberati)\ **Post date:** [January 19, 2022, 4:34pm UTC](https://discuss.elastic.co/t/max-between-two-fields-in-last-x-days-kibana-lens/294765/15 "2022-01-19T16:34:09Z")
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. ---
**Author:** ![dash5000](https://avatars.discourse-cdn.com/v4/letter/d/c6cbf5/32.png) [@dash5000](https://discuss.elastic.co/u/dash5000)\ **Post date:** [January 19, 2022, 5:05pm UTC](https://discuss.elastic.co/t/max-between-two-fields-in-last-x-days-kibana-lens/294765/16 "2022-01-19T17:05:48Z")
Ya will try this out. Thanks ---
**Author:** ![dash5000](https://avatars.discourse-cdn.com/v4/letter/d/c6cbf5/32.png) [@dash5000](https://discuss.elastic.co/u/dash5000)\ **Post date:** [January 20, 2022, 4:05am UTC](https://discuss.elastic.co/t/max-between-two-fields-in-last-x-days-kibana-lens/294765/17 "2022-01-20T04:05:03Z")
@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 ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [February 17, 2022, 4:05am UTC](https://discuss.elastic.co/t/max-between-two-fields-in-last-x-days-kibana-lens/294765/18 "2022-02-17T04:05:34Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.