Average days/employee

[#1]

Hi

I've just started to work with QW, and I have a problem. Would be really grateful if someone can help me or point in the right direction.

Simplified data from two tables

Table 1
EID	Unit	Section	Group	Employee
NN1	A	AA	AAA	1
NN2	A	AA	AAA	1
NN3	B	BB	BBB	1
NN4	B	BB	BBB	1
NN5	B	BB	BBB	1
NN6	C	CC	CCC	1
NN7	C	CC	CCC	1
Table 2
EID	Unit	Section	Group	Absence	No Days
NN1	A	AA	AAA	Sick leave	6
NN2	A	AA	AAA	Vacation	3
NN3	B	BB	BBB	Vacation	5
NN4	B	BB	BBB	Sick leave	5
NN7	C	CC	CCC	Sick leave	2
Outcome in table 3
Table3
Unit	No Days	Avg days/emp
A	9	4,5
B	10	3,3
C	2	2

(Unit is a hierarchy group)

Avg/emp i based och sum(no days)/sum(employee). If I want to se avg days/emp för sick leave only, what do I do to keep the total number of employees, i.e not only emplyees on sick leave?

//Daniel

[#2]

Set Analysis,

sum({$<Absence={[Sick leave]}>}no days)/sum(employee)

[#3]

Thanks for your help. That worked for a fixed table.

I think I need to be a bit more specific. I've got Absence in a Listbox and choose sick leave, then sum(employee) only counts EIDs that have been on sick leave. I need avg/emp to always be based on total number of employees regardless of the type of absence.

(I also need to be able to show avg/emp if I filter by section or group in hierarchy)

[#4]

not sure about syntax but try sum(all employee).

[#5]

Sum (all employee) gives the total sum for all units which means every unit gets the same total of employees.

I seems to me that I need to give every unit, section and group a "fixed" numer of employees so it doesnt reflect choises made in other listboxes. How do I do that?

Thanks for your effort anyway.