How can we use "Group By" separately for 2 Metrics in Metric Visual Builder?

Hi, I am stucked in some calculation. I want to calculate following from an index which is having a merged data of employees who left the company and employees who are still working in the company. So, I have 2 types of documents in a single index:
a) Workday Data(Employees working in the company)
b) Attrition Data(Employees who left the company)

Now, I want to calculate Average Headcount and Attrition Rate

  1. Opening Headcount and Closing Headcount) These are required to calculate Average Headcount, where Opening Headcount is constant and Closing Headcount is the Count of documents of workday type data.

My calculation to calculate Closing Headcount is below-


  1. Attrition Rate) In my index data, there are 2 types of attrition- Voluntary & Involuntary attrition.
    I want to calculate the Voluntary Attrition Rate and Involuntary Attrition Rate.

To calculate them, again I need to apply a filter/terms in Group By feature. To do that, I added a new series in Metric Visual Builder as below-


Now, the problem is that I need to show the result of calculation as follows:
(Result of Series 1) / (Result of Series 2)

Please reply on this as soon as possible. I need to do it urgently.

For the second part, you can add a Math aggregation which allows you to do division like that.
As for the first part, it's a bit more tricky. Elasticsearch isn't made to work with one metric over the whole series, more like for each bucket.
What you could try to in order to get the average headcount (assuming the attrition data also contains hirings) would be to do an Average for the Cumulative Sum of attrition data. That should come out as the average headcount in the end.

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