Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

marley1978
New Contributor II

Help with Aggr

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

ReportMonthEmpIDActive FlagNew Hire FlagGender
01/2018110F
01/2018210F
01/2018301F
01/2018410M
01/2018501M
02/2018110F
02/2018200F
02/2018310F
02/2018400F
02/2018510M
02/2018601M
02/2018701M
02/2018801M

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

1 Solution

Accepted Solutions

Re: Help with Aggr

May be this

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

7 Replies

Re: Help with Aggr

May be this

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

marley1978
New Contributor II

Re: Help with Aggr

Thank you so much.  It worked

Digvijay_Singh
Honored Contributor III

Re: Help with Aggr

I came up with this -

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

Re: Help with Aggr

Why an extra Aggr()?

Digvijay_Singh
Honored Contributor III

Re: Help with Aggr

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

Re: Help with Aggr

Seems to be working for me in QlikView

Capture.PNG

Did you try in View or Sense?

Digvijay_Singh
Honored Contributor III

Re: Help with Aggr

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

Capture.PNG

Community Browser