topic Re: AVG(Count) nested aggregation in App Development

AVG(Count) nested aggregation

Joosh — Mon, 31 Jan 2022 16:33:31 GMT

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

Re: AVG(Count) nested aggregation

chrismarlow — Mon, 31 Jan 2022 17:20:17 GMT

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.

Re: AVG(Count) nested aggregation

Joosh — Mon, 31 Jan 2022 18:17:37 GMT

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

Re: AVG(Count) nested aggregation

chrismarlow — Mon, 31 Jan 2022 18:59:07 GMT

HI,

So something like this;

Cheers,

Chris.

Re: AVG(Count) nested aggregation

Joosh — Mon, 31 Jan 2022 19:35:22 GMT

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

Re: AVG(Count) nested aggregation

chrismarlow — Tue, 01 Feb 2022 12:08:49 GMT

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.

Re: AVG(Count) nested aggregation

Joosh — Tue, 01 Feb 2022 16:38:18 GMT

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

Re: AVG(Count) nested aggregation

chrismarlow — Tue, 01 Feb 2022 16:58:06 GMT

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.

Re: AVG(Count) nested aggregation

Joosh — Tue, 01 Feb 2022 17:24:51 GMT

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

Re: AVG(Count) nested aggregation

chrismarlow — Tue, 01 Feb 2022 17:29:32 GMT

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.

Re: AVG(Count) nested aggregation

Joosh — Tue, 01 Feb 2022 18:54:02 GMT

I've updated it to use the {1} in the set analysis to ignore all selections. and it still is not liking it.

Josh