Filter ratio in TSVB

[hiba (chaieb)]

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 .

i need help please !

[markov00 (Marco Vettorello)]

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.

[hiba (chaieb)]

Hi @markov00 ,

thanks for reply.

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

[markov00 (Marco Vettorello)]

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:

[hiba (chaieb)]

thank you very much ,
it works fine