# Simple variation of counts - Serial differencing

**URL:** <https://discuss.elastic.co/t/simple-variation-of-counts-serial-differencing/277118>\
**Category:** Kibana\
**Created:** [June 26, 2021, 9:43am UTC](https://discuss.elastic.co/t/simple-variation-of-counts-serial-differencing/277118 "2021-06-26T09:43:04Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![efftee](https://avatars.discourse-cdn.com/v4/letter/e/77aa72/32.png) [@efftee](https://discuss.elastic.co/u/efftee)\
**Post date:** [June 26, 2021, 9:43am UTC](https://discuss.elastic.co/t/simple-variation-of-counts-serial-differencing/277118/1 "2021-06-26T09:43:05Z")

</div>

Hi Community,

The most apparently simplest of things, that you can do in seconds with a pen and paper, but spent hours trying in Kibana and failed so far. It's driving me completely crazy !!  
Try it and tell me 😛

So, I have data that is shipped every 3 hours about individual entities.  
The entities are identified by a unique keyword. Lets call it "id".  
So I'm examining the number of entities that are shipping data.

For example I have:

- count of unique records of "id" at "timestamp": "now-30d/d" = 800
- count of unique records of "id" at "timestamp": "now-3h/h" = 900

This is easily shown as a simple line in a time-series graph, unique counts of "id" over time.  
Dead simple.

In my example, we can all see that over 30 days there was an increase of +100 indiviual entities.

I want to visualize that metric. Just a simple panel with a number (not even in a time series).  
And am miserably failing to do so...

Basically the serial difference formula as follows:  
metric = (unique count of "id" at "timestamp": "now-3h/h") - (unique count of "id" at "timestamp": "now-30d/d")

I've tried lenses, tsvb, aggregations, TinyMaths, SQL, KQL, and never have I found the way to compare two counts from two different buckets.

I am so appalled at myself it's not funny...  
Please help me avoid telling my boss that we are paying hundreds of dollars for a beautiful elastic cloud stack, but that it can't tell him how many additionnal entities we've registered (or lost) over the past 30 days... 😱 🤕

---

<div class="post-metadata">

**Author:** ![efftee](https://avatars.discourse-cdn.com/v4/letter/e/77aa72/32.png) [@efftee](https://discuss.elastic.co/u/efftee)\
**Post date:** [June 27, 2021, 3:20pm UTC](https://discuss.elastic.co/t/simple-variation-of-counts-serial-differencing/277118/2 "2021-06-27T15:20:35Z")

</div>

Alternatively, could a runtime field script make counts of a specific field in specific time ranges ?  
(or is it as I fear only within one document at a time)

---

<div class="post-metadata">

**Author:** ![flash1293](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/flash1293/32/41227_2.png) [@flash1293](https://discuss.elastic.co/u/flash1293)\
**Post date:** [June 28, 2021, 8:02am UTC](https://discuss.elastic.co/t/simple-variation-of-counts-serial-differencing/277118/3 "2021-06-28T08:02:48Z")

</div>

You are right, runtime fields only operate on a single document.

For now, you can do this in Canvas using SQL and expressions like this:

```auto
filters
| essql 
  query="SELECT COUNT(DISTINCT clientip) as total_count FROM \"kibana_sample_data_logs\""
| math {string "total_count - " {filters | essql query="SELECT COUNT(DISTINCT clientip) as last_week_count FROM \"kibana_sample_data_logs\" WHERE timestamp < (NOW() - INTERVAL 7 DAY)" | getCell}}
| metric "Unique count difference (last week)" 
  metricFont={font size=48 family="'Open Sans', Helvetica, Arial, sans-serif" color="#000000" align="center" lHeight=48} 
  labelFont={font size=14 family="'Open Sans', Helvetica, Arial, sans-serif" color="#000000" align="center"} metricFormat="0,0.[000]"
| render

```

For people stumbling over this in the future: In a future version (most likely 7.14) you will be able to do the same thing in Lens using "Lens formula" in a much simpler way: `unique_count() - unique_count(kql="timestamp < now-1w")`.

---

<div class="post-metadata">

**Author:** ![efftee](https://avatars.discourse-cdn.com/v4/letter/e/77aa72/32.png) [@efftee](https://discuss.elastic.co/u/efftee)\
**Post date:** [June 28, 2021, 10:48am UTC](https://discuss.elastic.co/t/simple-variation-of-counts-serial-differencing/277118/4 "2021-06-28T10:48:14Z")

</div>

Thank you Joe,  
I will try this out in canvas (it's for reporting anyway), just a too bad we can't include it in a dashboard.. yet 😉  
Eager to see this new "Lens formula" feature appearing soon. Great job.  
Thanks for your support !

---

<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:** [July 26, 2021, 10:48am UTC](https://discuss.elastic.co/t/simple-variation-of-counts-serial-differencing/277118/5 "2021-07-26T10:48:47Z")

</div>

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