# Lens Formula subtract first and last document

**URL:** <https://discuss.elastic.co/t/lens-formula-subtract-first-and-last-document/330837>\
**Category:** Kibana\
**Tags:** lens, visualisation\
**Created:** [April 26, 2023, 12:31pm UTC](https://discuss.elastic.co/t/lens-formula-subtract-first-and-last-document/330837 "2023-04-26T12:31:54Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![deepack86](https://avatars.discourse-cdn.com/v4/letter/d/9de0a6/32.png) [@deepack86](https://discuss.elastic.co/u/deepack86)\
**Post date:** [April 26, 2023, 12:31pm UTC](https://discuss.elastic.co/t/lens-formula-subtract-first-and-last-document/330837/1 "2023-04-26T12:31:54Z")

</div>

I want to subtract the last and the first document from a timerange to check how much engergy was consumed. So i make a lens with a formula like this :  
(last\_value(value.value\_float32) - min(value.value\_float32)) . I take the min value because it seems there is no function for the "first" document. The syntax works, but only for ca. 11 hours.

 ![11h](https://us1.discourse-cdn.com/elastic/original/3X/3/4/34765a4c34d6a0d1c7a75ea915671b98485a155c.png)  
After that, the result of the min document is zero. so the visualtion will shw the "last" document. I checked the documents after 11 hours and there is no one document with a zero.  
 ![12h](https://us1.discourse-cdn.com/elastic/original/3X/3/8/388fb39024c24b107ee642005a374c00d4362a2f.png)

I also checked with other documents. Same result... So I don't get it, where my mistake is, or is it a bug? Maybe there is a another solution?

Greetz Thomas

Edit: I m working withe the Version 8.6.1

---

<div class="post-metadata">

**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:** [April 28, 2023, 8:31am UTC](https://discuss.elastic.co/t/lens-formula-subtract-first-and-last-document/330837/2 "2023-04-28T08:31:45Z")

</div>

Hi @deepack86

welcome to the Kibana community.  
In Kibana 8.7 the "previous" shift feature has been introduced which will allow you to perform a difference between the last value of the current time window and the previous one:

```auto
last_value(value.value_float32, kql='value.value_float32: *') - last_value(value.value_float32, kql='value.value_float32: *', shift='previous')

```

---

<div class="post-metadata">

**Author:** ![deepack86](https://avatars.discourse-cdn.com/v4/letter/d/9de0a6/32.png) [@deepack86](https://discuss.elastic.co/u/deepack86)\
**Post date:** [May 9, 2023, 8:05am UTC](https://discuss.elastic.co/t/lens-formula-subtract-first-and-last-document/330837/3 "2023-05-09T08:05:37Z")

</div>

> [@Marco\_Liberati](#):
>
> ```auto
> last_value(value.value_float32, kql='value.value_float32: *') - last_value(value.value_float32, kql='value.value_float32: *', shift='previous')
> 
> ```

Hi Marco\_Liberati,

thanks for your anwser! It works ver well... : )

Greetz Thomas

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [June 6, 2023, 8:05am UTC](https://discuss.elastic.co/t/lens-formula-subtract-first-and-last-document/330837/4 "2023-06-06T08:05:50Z")

</div>

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