Status as of a particular date (PeopleSoft Effective Date)

[#1]

My application is an HR dashboard using v9.00, showcasing data from PeopleSoft, which is built using "effective dates". This is the part that is giving me fits. I need to show historical data. How many employees were active on any given date in time. I will have a calendar, where the user can select a date and get the headcount based on their date selection. It SHOULD be fairly simple, however I can't figure out how to do it with the effective dating criteria within PS. The data looks like this:

Employee Effective Date Action HR Status

A 2/28/2008 New Hire Active

A 1/1/2009 Position Change Active

A 3/15/2009 Raise Active

A 6/20/2009 Lay-Off Inactive

A 12/1/2009 Recall Active

So, if the user selects the date of 8/1/2009, the employee should show as inactive, because the last effective date prior to the selected date had an HR Status of Inactive. If the user selected any date between 2/28/08 and 6/19/2009, the employee would count as an active employee.

I'm not an advanced user, so I could use some help with this! This is basically the "key" to being able to move any further on my application. I'm stuck without it!

Thanks to everyone!

Jennifer

[johnw]

OK, here's a completely different way to handle it, based on the earlier example of PCs working or being repaired. It should not have the memory problem on the load. On the down side, you probably WILL have some significant performance problems in some kinds of charts. For instance, in the chart of active employees by date, all of the heavy lifting is being done by a sum(if(...)), which processes EVERY record for EVERY row. Best if you can try to avoid that sort of chart if you're using this method.

[#3]

Just to say that the first intervalmatch approach helped me enormously.

A smaller data set, (800 users over 5 years) but four different datebound facts including active/inactive log dates.

Absolutely no performance issues (touch wood)!

If I could flag this as a helpful answer, I would!

[johnw]

In case it's useful, here's a more recent example I made.  It's using a while loop instead of an intervalmatch, which can be simpler and more efficient in some cases.