AVG(Count) nested aggregation

[Joosh]

I'm trying to get a YTD average of a count of employees

I use this formula to count the employees

COUNT(Global Career Band) in a line chart, my date field is called MonthName, in format 'MMM-YY'

How can i use this chart to show a YTD average, so on January it would show 27257, but on february it would take the average of february and january and so on.

Thanks

[chrismarlow]

Hi,

I think you would use rangeavg/above wrapped around the count, but would need to know more about the data model to give more.

Cheers,

Chris.

[Joosh]

Thanks for this,

Every month has a csv where each row is one employee, one of the columns in this is “Global Career Band”, and I’m just currently counting every row for every month which is giving me employee count, for example there would be 27257 rows of data in January File. Hopefully that covers the model

[chrismarlow]

HI,

So something like this;

Cheers,

Chris.

[Joosh]

Hi Chris,

So this is almost there, i will show you my result, please ignore all the filter exclusions in the count.

So what i want is it should not include prior year values in the average, for example the YTD average for Jan 20 should be 27645 rather than average of Jul-19 and Jan-20. Every new year the average should reset if that makes sense? Could you add some 2021 values and show me how that work?

Thanks for that range above solution, it has helped me get alot closer than i was.

Josh

[chrismarlow]

Hi,

So as long as your data is continuous you could try something like the below;

Note I think this does start to fail if you start to slice & dice and get missing months.

Cheers,

Chris.

[Joosh]

So I tried what you tried and i ended up getting NULLs, i then created a new column called MonthCode shown in the 4th column and used this in the expression like so

RangeAvg(Above(COUNT([Global Career Band]),0,MonthCode))

as you can see below getting NULL

Thanks

Josh

[chrismarlow]

Hi,

I cannot really tell from your screenshot what the issue is.

My Month(MonthName) works as MonthName was created using MonthName() in script. Is your MonthCode column just Month(MonthName), or something else?

If you share the full formulae that are not working (either in screenshot by making columns wider, or paste them in) might be clearer.

Cheers,

Chris.

[Joosh]

Hi here you go, so i'm excluding a few selections (as you can see in the formulas) that are always applied on the dashboard, i want this chart to ignore some specific selections. I've used your Month(MonthName) also in this method

[chrismarlow]

Hi,

That is odd. Looks like you might have missing right bracket at the end .. but then you get 0 for the first row. I think the set analysis bit in there is a red herring.

Cheers,

Chris.