XY bar time series difficulty in determining expressions

[dwooding]

I'm attempting to create a couple of charts detailing hiring, terminating, and headcount data. I have been successfully able to create an XY time series bar chart for hires by month and terminations by month, but I'm having a great deal of difficulty with the creation of the right variables and expressions for the headcount chart.

Headcount value per month: I need a variable that counts a record for each month if that month falls between the original hire date and the termination date. This needs to reset for each month

Time period: The time period should be controlled by a start and end date parameter. These parameters I've had no problem setting up and my graphs seem to obey their limits fine.

Month: The month variable needs to obey the time period. Within the graph the first month should be the start date month and the last should be the end date month.

The end result should be a bar chart that shows a date range in months on the x access and the value (counted if that particular month falls between hire and termination dates) for each month on the y access.

It seems simple until I actually try to do it. I'm just having a problem wrapping my head around it all. The original two charts I did for hiring and termination were quite simple using the term and hire dates to calculate the month and grouping by that month, but this isn't.

Any help or insight is appreciated!

[jpauze]

Where is your data source coming from?  Database? I only ask because this would be a fairly straight forward SQL statement to pre-aggregate/group the data before it even got to the report/chart.  If database, post a sample of the data and I will see if I can help out.

Otherwise, maybe mock up what you are trying to do and re-post,  myself, i was having trouble following the logic and do want to help out.

[dwooding]

The cource is currently an ODBC connection to an access database with linked excel tables (this is really a temporary setup which is why I'm not bothering with something more elegant). Eventually we will be moving to a MySQL database.

 

I've attached an example of the three charts I've been working on and below are some specifications that might help explain where my thinking is coming from.

New Hires:

Group: HireDate -- Expression: $V{HMonth}
Variables:

HireDateTotal

Class: Int
Expression: $V{HMonth}
Calc: Count
Reset Type: Group
Reset Group: HireDate

HMonth

Class: Int
Expression: $F{orig_hire}.getMonth() + 1
Calc: Nothing
Reset Type: Report

Parameter: InputDate
Filter: $F{orig_hire} >= $P{InputDate}
Sort: HMonth

Chart Type: Time series (YX bar)

Series Expression: $V{HMonth}
Time Period: $F{orig_hire}
Value: $V{HireDateTotal}
 

Terminations

This is almost the same as Hires just with the relevant fields etc. relating to term_dt rather than orig_hire.

Both the Terminations and Hire graphs work fine this way. No issues. My understanding is that the Y access is controlled by the HireDateTotal, while the X access is controlled by the hire month with the value reset and incrimented on the Hire Month Group. The sort is also particularly important as the values would not be correct without it.

For the third graph, headcount, for every month within a range (2 parameters start and end date) resetting on the month, the value of Y should be the total # of rows where the month falls between the original hire date and termination date(if it exists).

I don't know if that makes it anymore or less clear what I'm trying to do.

Right now this is what I have for the headcount chart:

 

Group: DateGroup -- Expression: $V{Month}
Variables:

HeadCountTotal

Class: Int
Expression: ( ($F{orig_hire}.getMonth()+1) <= $V{Month} && $V{Month} <= ($F{term_dt}.getMonth()+1) ? $V{HeadCountTotal}+1 : $V{HeadCountTotal}+0 )
Calc: Count
Reset Type: Group
Reset Group: DateGroup

Month

Class: Int
Expression: ???
Calc: ???
Reset Type: Report

Parameter: StartDate & EndDate
Filter: $F{orig_hire} >= $P{StartDate}
Sort: Month

Chart Type: Time series (YX bar)

Series Expression: $V{Month}
Time Period: ???
Value: $V{HeadCountTotal}

Obviously the thing that's tripping me up a lot is the time period/month information, but I'm not confident that the rest of it is right either.