i need to build a gauge which predicts departures of the workers basing on the departures we had until today. Division is by Average headcount which was in the last year until today. The formula should completely be dynamic by company, business line, branch, job_group etc. The only dimension which impacts the nominator , but not denominator is action_reason. Meaning that when I filter by action_reason, if alone or together with other filters, only nominator is being impacted, denominator stays the same as was before filtering for action_reason.
i am attaching here denominator only, as it does not work well while filtering action_reason if alone or together with other dimensions. I tried in chat gpt few suggestions, but nothing worked. The field action_reason is in the key table alone with bl_desc and job_group.
This is kind of hard to parse - there seems to be a State involved based on the syntax which you didn't detail and there's a lot of text - but I would suggest looking at the scope for the Avg() and Aggr() functions. You seem to have only applied set analysis to the internal count() aggregation but left those two unscoped.
Unfortunately, without your dataset, this isn't something that I can help troubleshoot based on the information you've provided. I can only offer the general suggestion that you look at the scope/set for the external aggregation functions (Avg and Aggr) as a possible source of issues. Reiterating the same formula doesn't provide new direction.
