How to do this in script

[ali_hijazi]

Dear I got a QVW document in which there is a table for employees where there are three columns:

EmployeeID, StartDate, EndDate

1, 2/7/2005,NULL

2,1/1/2009,NULL

....

44,5/5/2007, 16/7/2012

Now I want on a chart to display the number of active employees by year and month

the years start from 2004

so for the above sample data in 2004 none of the employees above should be counted

in 2005 only employee one should be counted

in 2006, and 2008 only employee 1 is counted

in 2007 employee only 1 is counted but in may 1  and 44 are counted

in 2009,2010,2011 1,2, and 44 are counted

in 2012 before July only 1, and 2 are counted

but July and later all above employees are counted

now how to add records by year and month so that the above scenario is achieved

[#2]

please send qvw file

[jonathandienst]

Hi

This is how I would approach the problem. Please see attached.

Data:
LOAD * Inline
[
EmployeeID, StartDate, EndDate
 1, 2007/11/28, 
 2, 2012/03/16, 2012/06/27
 3, 2008/02/20, 
...
40, 2002/08/19, 2009/09/13
];
Join (Data)
LOAD RowNo()+2000 As Year
AutoGenerate 12;
Results:
LOAD *,
          If ((Year(StartDate) <= Year) And (Len(EndDate) = 0 Or Year(EndDate) >= Year), 1, 0) As InEmploy
Resident Data;
DROP Table Data;
LOAD Year,
          Sum(InEmploy) As Complement
Resident Results
Group By Year;

Regards

Jonathan

[nstefaniuk]

Else you create a Fact table at month level where you pre-calculate (solution of Jonathan Dienst). The sum of the year will be the total of all months of the year (so *12 if we imagine there is no movement).

Else you create intervals (from - to) on which you link the employees if they are presents, and you do the count in the dashboard. This solution allow you to count employee only 1 time if you decide to display data per year instead of per month. The single interval is in fact the month itself, but you can aggregate more by doing intervals of n months if there is no movement in this interval of n months.