Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
May be this
Avg(Aggr(Count({<[Active Flag]= {'1'}>+<[New Hire Flag] = {'1'}>} EmpID), ReportMonth, Gender))
May be this
Avg(Aggr(Count({<[Active Flag]= {'1'}>+<[New Hire Flag] = {'1'}>} EmpID), ReportMonth, Gender))
Thank you so much. It worked
I came up with this -
Aggr(nodistinct Avg(Aggr(Sum([Active Flag])+Sum([New Hire Flag]),Gender,ReportMonth)),Gender)
Why an extra Aggr()?
When Chart dimension is Only Gender,then it wasn't working, showing '-'. Worked when surrounded by extra aggr.
Seems to be working for me in QlikView
Did you try in View or Sense?
My bad, may be I had something else when I saw '-' - both works in sense too -