topic Aggregate Average? in QlikView

Aggregate Average?

Tue, 25 Nov 2014 23:00:21 GMT

Hi,

Can someone help me with below.

I have a formula that sums number of hours across a period of time.

I am dividing this by the unique employees over the same period so I can see the average amount of hours worked per employee.

Attached is basically the output. As you can see, there is a gap of 2. Can someone help me explain what I can do to fix this?

The only formula I'm using is sum(hours)/count(distinct(employees)).

This issue is, the line chart does not total to 62 but rather 64.

You can see the unique employees for each month. The total unique employees is 127 (perhaps some more individuals were added/removed each month).

Re: Aggregate Average?

JonnyPoole — Wed, 26 Nov 2014 03:22:36 GMT

One thought:

count( distinct employees) will count the unique employees over the 3 months. If the same employee was in more than one month, it will NOT double count that employee. Is that what you want ?

To count unique employees in each month and then add up the monthly totals you could modify to

count( distinct Employees&Month)

... so that if the same employee was in multiple months , they would EACH count toward the grand total.

sum(hours) will not have this issue

Re: Aggregate Average?

Wed, 26 Nov 2014 03:32:26 GMT

try this,

floor(sum(hours)/count(distinct(employees)))

Re: Aggregate Average?

jagan — Wed, 26 Nov 2014 03:58:36 GMT

Hi,

Try this expression to get month wise employee count and sum upto total

count( distinct Employees&Month)

Sum(Aggr(Count(Distinct Employees), Month))

Hope this helps you.

Regards,

Jagan.

Re: Aggregate Average?

jonathandienst — Wed, 26 Nov 2014 05:37:22 GMT

This is because you are trying to add ratios with different divisors (unique employees). In other words, the sum of the ratios (64) is not the same as the ratio of the sums (62). To sum the ratios, use this:

Sum(Aggr(sum(hours)/count(distinct(employees)), MONTH))

where you must replace MONTH with the correct month field name.

HTH

Jonathan

Re: Aggregate Average?

Wed, 26 Nov 2014 05:45:57 GMT

Thanks everyone.

I ended up using sum(WeeklyHours)/count(distinct(Employees&[Dimension]))