I have a monthly labor head count report that is driven by a date Amper variable (PERIOD_DT).

I user select the date (DD/M/YY) from a pull down menu and the report shows the headcount for that Month.

I would like to add a column before the headcount for the same month, but from the previous years month.

Maybe like:

DEFINE FILE PREVIOUS
Previous Year/HYYMDI = (&LBR_EMPLOYEE_COUNTS.LBR_EMPLOYEE_COUNTS.PERIOD_DT - 100) ???

Someway I need to create a column that has the headcounts from the same month of the previous year.

I have tried &SELECTDATE and some other suggestions from the forum but I can not seem to get it right.

Here is my code and a sample of the data:

 
DEFINE FILE LBR_EMPLOYEE_COUNTS
JobTitle/A50 = LCWORD(50, JOB_TITLE, 'A50'); -* Convert job title to First Letter Caps
CompanyName/A50 = LCWORD(50, COMPANY_NAME, 'A50'); -* Convert Company Name to First Letter Caps
FULLTIME/P6 MISSING ON = IF LBR_EMPLOYEE_COUNTS.LBR_EMPLOYEE_COUNTS.FT_JOB_NBR NE ' ' THEN 1 ELSE MISSING; -*marks fulltime
PARTTIME/P6 MISSING ON = IF LBR_EMPLOYEE_COUNTS.LBR_EMPLOYEE_COUNTS.PT_JOB_NBR NE ' ' THEN 1 ELSE MISSING; -*marks parttime
END
TABLE FILE LBR_EMPLOYEE_COUNTS
PRINT
     JobTitle
	 FULLTIME AS 'FT'
	 PARTTIME AS 'PT'
	 LBR_EMPLOYEE_COUNTS.LBR_EMPLOYEE_COUNTS.PERIOD_DT NOPRINT -*selectable param
         LBR_EMPLOYEE_COUNTS.LBR_EMPLOYEE_COUNTS.EMP_COUNT AS 'Current Year'
	 BY  LOWEST LBR_EMPLOYEE_COUNTS.LBR_EMPLOYEE_COUNTS.BUSINESS_UNIT NOPRINT SUB-TOTAL UNDER-LINE SUMMARIZE MULTILINES 
BY LOWEST CompanyName NOPRINT
BY LOWEST JobTitle NOPRINT
END

My data looks like this:

Job_Title|Pay Grade|FT|PT|Company|Address_Nbr|Company_Name|BUSINESS_UNIT|Period_DT|Emp_Count
ADMINISTRATIVE ASSISTANT I |6|GA9010|6|511|0|Admin|51171|1/1/2013|1
ADMINISTRATIVE ASSISTANT I |6|GA9010|6|511|0|Admin|51171|2/1/2013|0
ADMINISTRATIVE ASSISTANT I |6|GA9010|6|511|0|Admin|51171|3/1/2013|1
ADMINISTRATIVE ASSISTANT I |6|GA9010|6|511|0|Admin|51171|4/1/2013|1
ADMINISTRATIVE ASSISTANT I |6|GA9010|6|511|0|Admin|51171|5/1/2013|1
ADMINISTRATIVE ASSISTANT I |6|GA9010|6|511|0|Admin|51171|6/1/2013|1
ADMINISTRATIVE ASSISTANT I |6|GA9010|6|511|0|Admin|51171|7/1/2013|1
ADMINISTRATIVE ASSISTANT I |6|GA9010|6|511|0|Admin|51171|8/1/2013|1
ADMINISTRATIVE ASSISTANT I |6|GA9010|6|511|0|Admin|51171|9/1/2013|1
ADMINISTRATIVE ASSISTANT I |6|GA9010|6|511|0|Admin|51171|10/1/2013|1
ADMINISTRATIVE ASSISTANT I |6|GA9010|6|511|0|Admin|51171|11/1/2013|1
ADMINISTRATIVE ASSISTANT I |6|GA9010|6|511|0|Admin|51171|12/1/2013|1
ADMINISTRATIVE ASSISTANT I |6|GA9010|6|511|0|Admin|51171|1/1/2014|1

This message has been edited. Last edited by: <Kathryn Henning>,

You want to combine headcount instances from two separate data-source records into a single report row.

The most straightforward approach is to use MATCH FILE to assemble the data, and then report against its HOLD output.

My brains thinks better in terms of dates formatted as YYYY/MM/DD but I'm sure you can follow the sample below and adjust to your needs

First, you'll want to determine which date boundaries to use *before* attempting to query your database

-DEFAULT &PERIOD_DT = '2014/03/07';

-* Convert specified period to a 'date'
-SET &PERIOD_DT_DATE = DATECVT(EDIT(&PERIOD_DT, '9999$99$99'), 'A8YYMD', 'YYMD');

-* Current month dates
-SET &CURR_YR_MTH_STR_DATE = DATEMOV(&PERIOD_DT_DATE, 'BOM');
-SET &CURR_YR_MTH_END_DATE = DATEMOV(&PERIOD_DT_DATE, 'EOM');

-* Corresponding dates for previous year
-SET &PREV_YR_MTH_STR_DATE = DATEADD(&CURR_YR_MTH_STR_DATE, 'Y', -1);
-SET &PREV_YR_MTH_END_DATE = DATEMOV(&PREV_YR_MTH_STR_DATE, 'EOM');
-RUN
-* Turn 'dates' into 'strings'
-SET &CURR_YR_MTH_STR = EDIT(DATECVT(&CURR_YR_MTH_STR_DATE, 'YYMD', 'A8YYMD'), '9999/99/99');
-SET &CURR_YR_MTH_END = EDIT(DATECVT(&CURR_YR_MTH_END_DATE, 'YYMD', 'A8YYMD'), '9999/99/99');
-SET &PREV_YR_MTH_STR = EDIT(DATECVT(&PREV_YR_MTH_STR_DATE, 'YYMD', 'A8YYMD'), '9999/99/99');
-SET &PREV_YR_MTH_END = EDIT(DATECVT(&PREV_YR_MTH_END_DATE, 'YYMD', 'A8YYMD'), '9999/99/99');

-TYPE Period     : &PERIOD_DT
-TYPE Start      : &CURR_YR_MTH_STR
-TYPE End        : &CURR_YR_MTH_END
-TYPE Prev Start : &PREV_YR_MTH_STR
-TYPE Prev End   : &PREV_YR_MTH_END

So, assuming the user chooses today as the Period Date, you'll have this;

Period     : 2014/03/07
Start      : 2014/03/01
End        : 2014/03/31
Prev Start : 2013/03/01
Prev End   : 2013/03/31

All you need now is to query the data and bucket your measurements by Year/Month.

Do you by any chance have a Calendar table in your database you can use to derive Year/Month directly there so the aggregations can be performed by the DB server? This would be the recommended approach for efficiency.

JOIN PERIOD_DT IN LBR_EMPLOYEE_COUNTS TO CALENDAR_DT IN CALENDAR TAG J0 AS J0

TABLE FILE LBR_EMPLOYEE_COUNTS
SUM
    SUM.EMP_COUNT
BY DEPARTMENT
ACROSS  J0.YR_MTH   <-- Assuming this exists in your Caldendar table/dimension
WHERE (PERIOD_DT FROM '&PREV_YR_MTH_STR' TO '&PREV_YR_MTH_END') OR
      (PERIOD_DT FROM '&CURR_YR_MTH_STR' TO '&CURR_YR_MTH_END') OR
END

This message has been edited. Last edited by: njsden,

Given that a Calendar dimension/table is not always available to simplify our lives, this is a way to make it happen "the FOCUS way"

-DEFAULT &PERIOD_DT = '2014/03/07';

-* Convert specified period to a 'date'
-SET &PERIOD_DT_DATE = DATECVT(EDIT(&PERIOD_DT, '9999$99$99'), 'A8YYMD', 'YYMD');

-* Current month dates
-SET &CURR_YR_MTH_STR_DATE = DATEMOV(&PERIOD_DT_DATE, 'BOM');
-SET &CURR_YR_MTH_END_DATE = DATEMOV(&PERIOD_DT_DATE, 'EOM');

-* Corresponding dates for previous year
-SET &PREV_YR_MTH_STR_DATE = DATEADD(&CURR_YR_MTH_STR_DATE, 'Y', -1);
-SET &PREV_YR_MTH_END_DATE = DATEMOV(&PREV_YR_MTH_STR_DATE, 'EOM');
-RUN
-* Turn 'dates' into 'strings'
-SET &CURR_YR_MTH_STR = EDIT(DATECVT(&CURR_YR_MTH_STR_DATE, 'YYMD', 'A8YYMD'), '9999/99/99');
-SET &CURR_YR_MTH_END = EDIT(DATECVT(&CURR_YR_MTH_END_DATE, 'YYMD', 'A8YYMD'), '9999/99/99');
-SET &PREV_YR_MTH_STR = EDIT(DATECVT(&PREV_YR_MTH_STR_DATE, 'YYMD', 'A8YYMD'), '9999/99/99');
-SET &PREV_YR_MTH_END = EDIT(DATECVT(&PREV_YR_MTH_END_DATE, 'YYMD', 'A8YYMD'), '9999/99/99');
-SET &CURR_YR_MTH     = EDIT(&CURR_YR_MTH_STR, '9999999');
-SET &PREV_YR_MTH     = EDIT(&PREV_YR_MTH_STR, '9999999');

-TYPE Period Dt  : &PERIOD_DT
-TYPE Curr Start : &CURR_YR_MTH_STR
-TYPE Curr End   : &CURR_YR_MTH_END
-TYPE Curr Yr/Mth: &CURR_YR_MTH
-TYPE Prev Start : &PREV_YR_MTH_STR
-TYPE Prev End   : &PREV_YR_MTH_STR
-TYPE Prev Yr/Mth: &CURR_YR_MTH

-* Query employee counts by day, for the month period in current and previous year 
-* bucketing results by Year/Month
TABLE FILE LBR_EMPLOYEE_COUNTS
SUM
    SUM.EMP_COUNT
COMPUTE YR_MTH/A7 = EDIT(DATECVT(PERIOD_DT, 'YYMD', 'A8YYMD'), '9999/99');
BY PERIOD_DT   <-- Daily breakdown
BY DEPARTMENT
WHERE (PERIOD_DT FROM '&PREV_YR_MTH_STR' TO '&PREV_YR_MTH_END') OR
      (PERIOD_DT FROM '&CURR_YR_MTH_STR' TO '&CURR_YR_MTH_END') OR
ON TABLE HOLD AS HCOUNTS
ON TABLE SET ASNAMES ON
ON TABLE SET HOLDLIST PRINTONLY
END

-* Summarize results
TABLE FILE HCOUNTS
SUM
    EMP_COUNT
BY DEPARTMENT
ACROSS YR_MTH
END

Please note that the sample code provided assumes a YYMD/DATE field available. If PERIOD_DT is declared in your synonym as a date-time field instead (HYYMDx), you have 2 choices:

1. Adjust the code using appropriate WebFOCUS date-time functions
-or-
2. My preferred way, just add an extra field declaration to the synonym with a different FIELDNAME but same ALIAS (PERIOD_DT) with ACTUAL=DATE and USAGE=YYMD, and refer that field in the code instead.

Hope this gives some clues as to what you can do.

quote:

Do you by any chance have a Calendar table in your database you can use to derive Year/Month directly there so the aggregations can be performed by the DB server? This would be the recommended approach for efficiency.

I just got out of a meeting with the DBA and he also agreed this would be best done on the SQL side with a stored procedure to grab the Two corresponding records (jan 12- jan 13) from separate rows and place them on the same row.

Greg,

I don't get it: in your example there is no screening condition.

  
DEFINE FILE LBR_EMPLOYEE_COUNTS
JobTitle/A50 = LCWORD(50, JOB_TITLE, 'A50'); -* Convert job title to First Letter Caps
CompanyName/A50 = LCWORD(50, COMPANY_NAME, 'A50'); -* Convert Company Name to First Letter Caps
FULLTIME/P6 MISSING ON = IF LBR_EMPLOYEE_COUNTS.LBR_EMPLOYEE_COUNTS.FT_JOB_NBR NE ' ' THEN 1 ELSE MISSING; -*marks fulltime
PARTTIME/P6 MISSING ON = IF LBR_EMPLOYEE_COUNTS.LBR_EMPLOYEE_COUNTS.PT_JOB_NBR NE ' ' THEN 1 ELSE MISSING; -*marks parttime
END
TABLE FILE LBR_EMPLOYEE_COUNTS
PRINT
     JobTitle
	 FULLTIME AS 'FT'
	 PARTTIME AS 'PT'
	 LBR_EMPLOYEE_COUNTS.LBR_EMPLOYEE_COUNTS.PERIOD_DT NOPRINT -*selectable param
         LBR_EMPLOYEE_COUNTS.LBR_EMPLOYEE_COUNTS.EMP_COUNT AS 'Current Year'
	 BY  LOWEST LBR_EMPLOYEE_COUNTS.LBR_EMPLOYEE_COUNTS.BUSINESS_UNIT NOPRINT SUB-TOTAL UNDER-LINE SUMMARIZE MULTILINES 
BY LOWEST CompanyName NOPRINT
BY LOWEST JobTitle NOPRINT
END

Other questions:
you seem to hint that data from the previous year is in another table. Is it?
what is the format of your date in the database?
what are you receiving from the form?

It sounds to me like you have a simple problem and are unnecessarily complicating it.

All you have to do is something roughly along these lines:

DEFINE FILE XYZ
MONTH/M=&YYMD;  (OR CAPTURE FROM AN HTML INPUT AS AN AMPER VARIABLE)
THISYEAR/YY=&YYMD;
LASTYEAR/YY=THISYEAR - 1;  (OR USE DATEADD)
PERIOD_YEAR/YY=PERIOD_DT;
PERIOD_MONTH/M=PERIOD_DT;
END

TABLE FILE XYZ
PRINT {WHATEVER}
WHERE PERIOD_YEAR GE LASTYEAR
AND PERIOD_MONTH = MONTH;
END

quote:

you seem to hint that data from the previous year is in another table. Is it?
what is the format of your date in the database?
what are you receiving from the form?

You can see in the sample data I posted that the date from the previous year is in the same table. Just on a different row.

Jan 13 is on row 1. Jan 14 is on row 13. The last number on each row is the headcount.

Every month a reportcaster job is going to run and add a new row for each job title to the data base)

If the user chooses 'Jan, 2014' from the pulldown menu (all the params are in a global include file) I also want to grab 'Jan, 13' so I can compare the headcount difference Year over Year.

Each line in the report will look like this:

Job title---Jan 13---Jan 14-----increase---inc%

Bellman------30--------33--------3---------10%

I already have everything showing (the computes for the differences are being done in the database). I just need to show the head count from the previous years row (row 1 in my example) in a new column to the left of the current year (row 13).

As I already noted, to combine headcount instances from two separate data-source records into a single report row, you should think MATCH FILE.

To pull data for just a single month specified by an incoming parameter, the code would be something like shown below. (Note that, for prior-year headcount with respect to the report's target period, you pull the headcount in rows for a period such that adding a year to the row's period yields the target period of the report.)

DEFINE FILE LBR_EMPLOYEE_COUNTS
  ThisYearYYMD/YYMD=PERIOD_DT;
  NextYearYYMD/YYMD=DATEADD(ThisYearYYMD,'Y',1);
  JobTitle/A50 = LCWORD(50, JOB_TITLE, 'A50');
  CompanyName/A50 = LCWORD(50, COMPANYNAME, 'A50');
  FULLTIME/P6 MISSING ON = IF FT_JOB_NBR NE ' ' THEN 1 ELSE MISSING;
  PARTTIME/P6 MISSING ON = IF PT_JOB_NBR NE ' ' THEN 1 ELSE MISSING;
END

MATCH FILE LBR_EMPLOYEE_COUNTS
SUM
     FULLTIME
     PARTTIME
     PERIOD_DT
     EMP_COUNT
     PERIOD_DT
     BY BUSINESS_UNIT
     BY CompanyName
     BY JobTitle
WHERE ThisYearYYMD EQ '&PERIODYYMD';
RUN
FILE LBR_EMPLOYEE_COUNTS
SUM
     EMP_COUNT AS LY_EMP_COUNT
     BY BUSINESS_UNIT
     BY CompanyName
     BY JobTitle
WHERE NextYearYYMD EQ '&PERIODYYMD';
AFTER MATCH HOLD OLD
END
-RUN

TABLE FILE HOLD
PRINT
     FULLTIME AS 'FT'
     PARTTIME AS 'PT'
     EMP_COUNT AS 'Current Year'
     LY_EMP_COUNT AS 'Prior Year'
     BY PERIOD_DT NOPRINT
     BY BUSINESS_UNIT NOPRINT SUB-TOTAL UNDER-LINE SUMMARIZE MULTILINES
     BY CompanyName
     BY JobTitle 
END

That is a nice approach Jack. Even though the user is selecting current year with the dropdown, we grab the previous year then adding a year to get the current.

Can't wait for the office to open Monday morning so I can try it.

Thanks!