Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Community,
could you please help me with set analysis formula.
My goal is to calculate % male of all distinct leaders working in the company
My first step is to calculate number of male leaders. My formula looks like this. It gives close result, but not exact. If I add more Leadership Roles, result becomes less accurate.
avg(aggr(sum({< $(vPeriodType), PeriodYear={">2019<=2023"}, TimeFrame = {'Current Year'}, Gender={'Male'}, LeadershipRole={'Business', 'Academic'}>}[HeadCount]),[PeriodSKey],Gender))
vPeriodType shows if it "as of month" or "YTD" or "Rolling12 month" period.
Another filter that I need to add is that person can take several leadership roles at the same time (be Academic and Business Leader.) So I have to add distinct to my formula
Could you please help me to come up with formula to calculate % of male leaders working in the company
Thank you very much in advance!
Could you please replace with Gender in AGGR's dimention and repeat the same set analysis in the outer aggrigation as well?
avg({< $(vPeriodType), PeriodYear={">2019<=2023"}
,TimeFrame = {'Current Year'}
, Gender={'Male'}
, LeadershipRole={'Business', 'Academic', 'Center'}>}
aggr(
Count(DISTINCT
{< $(vPeriodType), PeriodYear={">2019<=2023"}
,TimeFrame = {'Current Year'}
, Gender={'Male'}
, LeadershipRole={'Business', 'Academic', 'Center'}>}
[PersonSKey])
,Gender))
Hello,
I guess that [HeadCount] has aggregated values. If yes, can you use the EmployeeIDs instead.
I had similar cases, but for changes in roles for any given EmployeeID which took place in a given period. Something like
count(DISTINCT
{< $(vPeriodType), PeriodYear={">2019<=2023"}
,TimeFrame = {'CurrentYear'}
, Gender={'Male'}
, LeadershipRole={'Business', 'Academic'}>}
EmployeeID)
Thank you. But it shows 0 as a result of calculation
Thank you so much!
Unfortunately, it doesn't give the right number
I tried this formula, it gives better result, but not the exact. I have total 54 people. But distinct should be 51. I'm getting 52.5 Maybe I can somehow improve it? Thank you very much for looking into it!
avg(
aggr(
Count(DISTINCT
{< $(vPeriodType), PeriodYear={">2019<=2023"}
,TimeFrame = {'Current Year'}
, Gender={'Male'}
, LeadershipRole={'Business', 'Academic', 'Center'}>}
[PersonSKey])
,[PeriodSKey]))
Could you please replace with Gender in AGGR's dimention and repeat the same set analysis in the outer aggrigation as well?
avg({< $(vPeriodType), PeriodYear={">2019<=2023"}
,TimeFrame = {'Current Year'}
, Gender={'Male'}
, LeadershipRole={'Business', 'Academic', 'Center'}>}
aggr(
Count(DISTINCT
{< $(vPeriodType), PeriodYear={">2019<=2023"}
,TimeFrame = {'Current Year'}
, Gender={'Male'}
, LeadershipRole={'Business', 'Academic', 'Center'}>}
[PersonSKey])
,Gender))
Thank you so much for your idea of count!
I removed period year filter and now everything is working fine!
Thanks a lot!