Kibana Canvas , Calculate percentage using to aggregations

[adityaPsl (Aditya)]

Hello Team,

I am working on a use case where I have to calculate percentage of two count aggregations

1. Get count of data from index1 with date filters
2. Get count of data from index1 with date and field filters
3. Calculate percentage using count1 and count2

Could you please help me understand how can we achieve this in Kibana Canvas.

Thank you for your help and support.

Thank you,
Aditya

[rashmi (kulkarni)]

@Catherine_Liu can you plz help here?

Thanks
Rashmi

[adityaPsl (Aditya)]

Hello Team,

If not possible using Canvas can we achieve this functionality using Vega.
Kibana version :6.7

Thank you for your help and support

Thank you,
Aditya

[Catherine_Liu]

This is a little complex, but I believe what you want is possible in Canvas. Essentially you need to query your index twice in the same expression to get the two different counts, and it also utilizes filter groups which was introduced in Kibana 7.2.0. I assume you wanted both counts to be filtered by the same time filter to filter your date field.

Here's the example using the ecommerce sample data set:

Time filter on order_date:

timefilterControl compact=true column="order_date" filterGroup="order_date"
| render

Dropdown filter on customer_gender:

esdocs index="kibana_sample_data_ecommerce" fields="customer_gender"
| dropdownControl valueColumn="customer_gender" filterColumn="customer_gender" filterGroup="gender"
| render

Metric that displays the percentage of orders made by the gender you're filtering by

filters group="order_date" group="gender" ungrouped=true
| essql 
    query="SELECT order_date, customer_gender FROM \"kibana_sample_data_ecommerce\""
    count=10000
| math { 
    string "count(customer_gender)/" 
      {filters group="order_date" ungrouped=true 
       | essql query="SELECT order_date, customer_gender FROM \"kibana_sample_data_ecommerce\""
           count=10000
       | math "count(customer_gender)"
      } 
  }
| formatnumber "0%"
| metric
  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"}
| render

[adityaPsl (Aditya)]

Thank you very much Catherine_Liu.
It helps understand few capabilities of Canvas.
However i am struggling to achieve the functionality, i will try to explain.
Lets say we are processing admissions ,We have date wise admissions count, and i want to calculate per day admission percentage with last 4 days admissions average count.

so if in last 4 days average admissions are 100 and today we have 10 admissions then percentage is 10%

so the first query would be : average count of last 4 days admissions.
second query would be : todays admission count

percentage = firstQueryResult*100/secondQueryResult.

can we add group by query to above use case like if i want to calculate class wise admission percentage

Could you please help me understand which visualsation would help in canvas.

Kibana Version :6.7

Thanks for your help and support.

Thank you,
Aditya