# Filter ratio in TSVB

**URL:** https://discuss.elastic.co/t/filter-ratio-in-tsvb/244842
**Category:** Kibana
**Created:** [August 13, 2020, 9:37am UTC](https://discuss.elastic.co/t/filter-ratio-in-tsvb/244842 "2020-08-13T09:37:56Z")
**Posts on this page:** 6
**Page:** 1

<div class="post-metadata">

### Author: ![hiba](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/hiba/32/110919_2.png) [@hiba](https://discuss.elastic.co/u/hiba)
#### Post date: [August 13, 2020, 9:37am UTC](https://discuss.elastic.co/t/filter-ratio-in-tsvb/244842/1 "2020-08-13T09:37:56Z")

</div>

Hi ,  
i use kibana 7.7.0

i want to calculate the occupancy rate in hotels: i have 5 documents in my index:

occupancy rate % = (sum of soldrooms)/ (sum of available rooms)

first : i create two scripted fields "sold-rooms" and "available-rooms":  
sold-rooms = doc['rooms.rates.availability.vendu'].sum()  
available-rooms = doc['rooms.rates.availability.dispo'].sum()  
they works fine and i test it in discover.

then i create a TSVB visualization to calculate the "occupancy rate" with the "filter ratio" but don't get any result .

 ![1](https://us1.discourse-cdn.com/elastic/original/3X/4/2/42f311f10da527d53fde2ef6c26f9463e9f164b5.png) ![2](https://us1.discourse-cdn.com/elastic/original/3X/a/2/a24bd774909660d788fc3f4f21e60953eff4ae2c.png)

i need help please !

---

<div class="post-metadata">

### Author: ![markov00](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/markov00/32/33316_2.png) [@markov00](https://discuss.elastic.co/u/markov00)
#### Post date: [August 13, 2020, 10:41am UTC](https://discuss.elastic.co/t/filter-ratio-in-tsvb/244842/2 "2020-08-13T10:41:07Z")

</div>

Hi @hiba  
the Filter ratio in TSVB accepts two filters written in KQL for the `Numerator` and `Denominator` and doesn't work as basic math script.  
FIlter Ratio can be applied in cases where you have your documents structured like the following:  
`doc1: {ratesAvailability: 10, status: vendu}`  
`doc2: {ratesAvailability: 5, status: dispo}`  
Where you want to have a ratio on the same aggregated field specifying two filter to distinguish between your numerator and denominator.  
In this example case, you can apply the Filter Ratio as the following: the numerator and denominator can be two filters on the `status` field (num: `status : vendu`, denom: `status : dispo` and the aggregation can be the sum of `ratesAvailability`

In your case instead, you can use a bucket script aggregation supplying two sum aggregation (you don't need the scripted fields): you can add two sum aggregations, one on `rooms.rates.availability.vendu` and the other on `rooms.rates.availability.dispo`, then you can add a bucket script that divides these two numbers.

---

<div class="post-metadata">

### Author: ![hiba](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/hiba/32/110919_2.png) [@hiba](https://discuss.elastic.co/u/hiba)
#### Post date: [August 13, 2020, 10:58am UTC](https://discuss.elastic.co/t/filter-ratio-in-tsvb/244842/3 "2020-08-13T10:58:48Z")

</div>

Hi @markov00 ,

thanks for reply.

the bucket script aggregation is not available .  
only the Metris aggregation are available.

 ![image](https://us1.discourse-cdn.com/elastic/original/3X/d/d/dda4000a60a32a56095c64b570a3f44c4d93b19b.png)

---

<div class="post-metadata">

### Author: ![markov00](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/markov00/32/33316_2.png) [@markov00](https://discuss.elastic.co/u/markov00)
#### Post date: [August 13, 2020, 11:09am UTC](https://discuss.elastic.co/t/filter-ratio-in-tsvb/244842/4 "2020-08-13T11:09:11Z")

</div>

so the method using the bucket script is not part of the Filter Ratio.  
First you have to create two Sum aggregation on your num/denom parameters and than apply the bucket script:

 ![Screenshot 2020-08-13 at 13.05.13](https://us1.discourse-cdn.com/elastic/original/3X/b/c/bccd3955d018eae359c42361410c53b91c44cb89.png)

---

<div class="post-metadata">

### Author: ![hiba](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/hiba/32/110919_2.png) [@hiba](https://discuss.elastic.co/u/hiba)
#### Post date: [August 13, 2020, 11:17am UTC](https://discuss.elastic.co/t/filter-ratio-in-tsvb/244842/5 "2020-08-13T11:17:09Z")

</div>

thank you very much ,  
it works fine

---

<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: [September 10, 2020, 11:17am UTC](https://discuss.elastic.co/t/filter-ratio-in-tsvb/244842/6 "2020-09-10T11:17:23Z")

</div>

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