Creating historical position from start/end dates

[#1]

Hi all,

I have a data set that consists, essentially, of staff name, start date, end date, 1 record per person.

What I'm trying to do is create a chart that displays a historical staff count, with month as the dimension, and count of staff employed within that month as the expression.

So a staff member whose start date was 05/02/15 and end date was 16/05/15 would be counted in Feb, Mar, April, May.

Does anyone have any suggestions?

Thanks in advance.

Tom

[fvelascog72 · Accepted solution]

Hi,

I think this could help:

Data:

LOAD

*,

Month(Start_Date) as "Month_Start_Date",

Month(End_Date) as "Month_End_Date"

Inline [

Staff_id, Start_Date, End_Date

1, 05/02/2015, 16/05/2015

2, 01/03/2015, 24/04/2015

];

NoConcatenate

Dates:

LOAD

  Max(End_Date) as "MaxDate",

  Min(Start_Date) as "MinDate"

Resident Data;

Let vMaxDate = Peek('MaxDate',0,'Dates');

Let vMinDate = Peek('MinDate',0,'Dates');

DROP Table Dates;

Right Join(Data)

LOAD Distinct

  Month(Date(RecNo() + $(vMinDate) - 1)) as "Month"

AutoGenerate $(vMaxDate) - $(vMinDate) + 1;

NoConcatenate

Data2:

LOAD

  *

Resident Data

Where Month >= Month(Start_Date) and Month <= Month(End_Date);

DROP Table Data;

And in the expression use: count(DISTINCT Staff_id)