Need help building a query to properly calculate turnover to be used in Vega. I tried looking at the context-must/context-must_not portions of Vega but I don't know if I can apply these to specific aggregations unless I do nested queries. The formula we're using for turnover is as follows.
(Terminations / ((Headcount in start month + Headcount in end month) / 2)) * 100
In the time filter, the user would select the desired date range and the min date would be the start month while the max date would be the end month. Terminations would include all term rows for all dates between start and finish.
According to Vega documentation, I should be able to use the following in order to apply the specific dates I want, but I need them to apply only to the specific their intended aggregations. Min should only apply to the Begin Headcount aggregation while max should only apply to the Ending Headcount
"min": {"%timefilter%": "min"}
"max": {"%timefilter%": "max"}
Data structure:
YR-MTH ACTIONTYPE RCDTYPE EMPLID
2021-01 Involuntary TER 1
2021-01 Active BGN 2
2021-01 Active END 2
2021-01 Active BGN 3
2021-01 Active END 3
2021-01 Active BGN 4
2021-01 Active END 4
2021-02 Active BGN 2
2021-02 Active END 2
2021-02 Involuntary TER 3
2021-02 Active BGN 4
2021-02 Active END 4
2021-03 Involuntary TER 4
2021-03 Active BGN 2
2021-03 Active END 2
So with this example if the user selects January 2021 to March 2021 the formula would be as follows.
Again Total terms across selected range divided by total BGN rows for min month, plus total END rows for final month, divided by two.
(TER / (( BGN + END ) / 2 )) * 100
( 3 / (( 3 + 1 ) / 2)) * 100
My current query only works when one month is selected. Thoughts on how to proceed?
"size": 0,
"aggs": {
"Turnover_Calculation": {
"terms": {
"script": "'try this'"
},
"aggs": {
"Turnover":{
"filter": { "term": { "VH_RCD_TYPE_ORCL_ES_NA_ENG":"TER" } },
"aggs" : {
"termcount": {
"value_count": {
"field": "ACTIONTYPE_ORCL_ES_NA_ENG"
}
}
}
},
"Headcount_BGN":{
"filter": { "term": { "VH_RCD_TYPE_ORCL_ES_NA_ENG": "BGN" } },
"aggs" : {
"headcount": {
"value_count": {
"field": "ACTIONTYPE_ORCL_ES_NA_ENG"
}
}
}
},
"Headcount_END":{
"filter": { "term": { "VH_RCD_TYPE_ORCL_ES_NA_ENG": "END" } },
"aggs" : {
"headcount": {
"value_count": {
"field": "ACTIONTYPE_ORCL_ES_NA_ENG"
}
}
}
},
"Turnover-Percent": {
"bucket_script": {
"buckets_path": {
"Headcount_BGN": "Headcount_BGN>headcount",
"Turnover": "Turnover>termcount",
"Headcount_END": "Headcount_END>headcount"
},
"script": "params.Turnover / ((params.Headcount_BGN + params.Headcount_END) /2) * 100"
}
}
}
}
}