topic Calculating values for each year/month from a Start Date in QlikView

Calculating values for each year/month from a Start Date

Mon, 28 Jun 2010 09:38:04 GMT

I have an HR application which provides a contract start date for each employee. I need to be able to show how many people are employed by the company by year/month from some date in the past to the current date.

Is there a way of doing this within QV (version 9 - personal edition)?

Calculating values for each year/month from a Start Date

Mon, 28 Jun 2010 13:05:08 GMT

Hi logan,

Here you are a possible solution. I hope it would help you.

You can check out the results making a table on the Total_Emp data.

Regards.


 Contract:
LOAD * INLINE [
 Date, Employee
 01/01/2009, E1
 01/01/2009, E2
 15/01/2009, E3
 20/02/2009, E4
 06/06/2009, E1
 10/12/2009, E5
 02/04/2010, E6
];
Calendar:
Load Distinct Year(Date) & '-' & Month(Date) as YM,
 Date
Resident Contract;
Total_Emp:
Load Year(Date) & '-' & Month(Date) as YM,
 Count(Employee) as TotalEmp
Resident Contract
group by Year(Date) & '-' & Month(Date);

Calculating values for each year/month from a Start Date

johnw — Tue, 29 Jun 2010 00:29:45 GMT

One approach is to actually create a record for every month the employee was employed. Here's an example (untested, and I'm assuming contracts can have ending dates as well):

[Employeess vs. Months]:
LOAD
"Employee"
,addmonths("Hire Month",iterno()-1) as "Month of Employment"
RESIDENT [Employees]
WHILE addmonths("Hire Month",iterno()-1) <= "Fire Month"
;

Chart:

Dimension = Month of Employment
Expression = count(distinct Employee)

You might also want to look at the intervalmatch() function for the same basic idea, just done differently.

Calculating values for each year/month from a Start Date

Tue, 29 Jun 2010 00:46:42 GMT

Hi there, you can use set analysis to limit artificially select a range of date. Your expressión might look some like this:

count({$<MonthYear = {">=PastMonthYear<=CurrentMonthYear"}>} EmployeeID)

That should do the trick.

Regards