Count all products with True values and show % of total

[Utkarsh_Khodke]

Hello, I'm new to Elasticsearch and Kibana and have a fairly basic question.

In the data loaded in Kibana, I have store_id and the number of products sold in each store. I also have a field call payment_status which is a boolean and shows true if payment has been made and false if payment is pending.

I'm trying to create a simple table visualisation that shows the top N stores with highest number of products sold and the % of payment status as True (let's call it payment_rate) for each store.

I researched a bit and found out that scripted fields is not the way to go about doing something like this as scripted fields do not accommodate for aggregated fields (count of store_id in our case).

I come from a background of Tableau and creating scripted fields was the first thought as it's closed to creating calculating fields on Tableau.

How can I achieve a viz like this in Kibana?

Please note: Field names have been edited as this is for work. Attached view of the data table:

[Tomo_M (Tomohiro Mitani)]

With lens, you can use formula:

sum('count',kql='payment_status:true')/sum('count')

to show payment_rate:

But one difficulty is the order of store_id is limited to "Alphabetical", at least in my 7.16. Using "aggregation-based", you can use custom metric to sort sotre_id, but is is not still implemented in lens. I'm not sure it is implemented in 8.0, but I hope it will in near future.

On the other hand, I found no way to calculate the rate in aggregation based data table.

[ghudgins (Graham Hudgins)]

For what it's worth, we currently have it on our 8.x plan to address the sorting limitations with custom formula - [Lens] Allow client-side sorting of dimensions and legends at datasource level for all chart types · Issue #86184 · elastic/kibana · GitHub

[Utkarsh_Khodke]

Thanks for your reply Tomohiro and Graham! Unfortunately, I don't see an option called 'lens' in my visualisation tab. When I click on çreate new visualisation' I can see many options but no option named lens. I'm not sure if it's because I'm on some different version or if my organisation hasn't 'enabled' the option. I'm on v7.8.0

Once again, apologies for these very basic questions as I'm new to Kibana and Elasticsearch

Edit: Just realised Lens is only available for versions 7.10 and above. link: Kibana Lens is now generally available | Elastic Blog

Is there anyway I can do this without using Lens?

[ghudgins (Graham Hudgins)]

Try the "Filter ratio" option in TSVB. Here you can provide KQL for the numerator and denominator to return a % value. Should be in the version of Kibana you're on.... (or you could upgrade )