Help with Aggr

[marley1978]

I have searched on this topic and have not found a solution

I am trying to get an average of active employees by month and gender

My data looks like this

ReportMonth	EmpID	Active Flag	New Hire Flag	Gender
01/2018	1	1	0	F
01/2018	2	1	0	F
01/2018	3	0	1	F
01/2018	4	1	0	M
01/2018	5	0	1	M
02/2018	1	1	0	F
02/2018	2	0	0	F
02/2018	3	1	0	F
02/2018	4	0	0	F
02/2018	5	1	0	M
02/2018	6	0	1	M
02/2018	7	0	1	M
02/2018	8	0	1	M

What I am trying to do is get the average  count of Active Flag + New Hire by month and gender.

I have this and this works by Month.

AVG(Aggr(count( {<ActiveFlag= {'1'}>} EmployeeID) + count( {< NewHireFlag= {'1'}>} EmployeeID), ReportMonth))

In my bar chart I have only gender as a dimension  but it doesn't do the calculations right.

My expected result based on the data above would look like this.

Average Female active employees for the both month = 2.5

Average Male active employees for both months = 3

Please help

[sunny_talwar · Accepted solution]

May be this

Avg(Aggr(Count({<[Active Flag]= {'1'}>+<[New Hire Flag] = {'1'}>} EmpID), ReportMonth, Gender))

[marley1978]

Thank you so much.  It worked

[Digvijay_Singh]

I came up with this -

Aggr(nodistinct Avg(Aggr(Sum([Active Flag])+Sum([New Hire Flag]),Gender,ReportMonth)),Gender)

[sunny_talwar]

Why an extra Aggr()?

[Digvijay_Singh]

When Chart dimension is Only Gender,then it wasn't working, showing '-'. Worked when surrounded by extra aggr.

[sunny_talwar]

Seems to be working for me in QlikView

Did you try in View or Sense?

[Digvijay_Singh]

My bad, may be I had something else when I saw '-' - both works in sense too -