Hello, I'm working on set analysis formula. I have to create a bar graph that will show distribution of people by departments, by age for a particular period of time(2021). During one year person can move from one department to another and from one age band to another(get older).
So I'm trying to create a formula with avg and aggr, but it's not working. Can you please help?
This is my formula
avg(aggr(avg({< Year = {'2021'}>}
aggr({< Year= {'2021'}>}
avg({<Year = {'2021'}>}
aggr({<Year = {'2021'}>}
sum({<Year= {'2021'}>}HeadCount),MonthID))
,AgeBand)),Dep1))
Also, I was trying this formula, but it is not giving me the exact result(close, but not exact number)
avg(aggr(sum({<Year = {'2021'}>}HeadCount),MonthID, AgeBand,Dep1))
Thanks a lot in advance!