Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Automatically grabbing previous years data

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Automatically grabbing previous years data
 Login/Join
 
Platinum Member
posted
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>,


prod: WF 7.7.03 platform IIS on Windows 2007, databases: Oracle, , MSSQL

 
Posts: 133 | Location: Orlando, FL | Registered: August 04, 2005Report This Post
Virtuoso
posted Hide Post
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.


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Virtuoso
posted Hide Post
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 Smiler

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,



Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
Virtuoso
posted Hide Post
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" Smiler

-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.



Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
Platinum Member
posted Hide Post
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.


prod: WF 7.7.03 platform IIS on Windows 2007, databases: Oracle, , MSSQL

 
Posts: 133 | Location: Orlando, FL | Registered: August 04, 2005Report This Post
Virtuoso
posted Hide Post
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?


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Master
posted Hide Post
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


WebFOCUS 7.7.05 Windows, Linux, DB2, IBM Lotus Notes, Firebird, Lotus Symphony/OpenOffice. Outputs PDF, Excel 2007 (for OpenOffice integration), WP
 
Posts: 674 | Location: Guelph, Ontario, Canada ... In Focus since 1985 | Registered: September 28, 2010Report This Post
Platinum Member
posted Hide Post
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).


prod: WF 7.7.03 platform IIS on Windows 2007, databases: Oracle, , MSSQL

 
Posts: 133 | Location: Orlando, FL | Registered: August 04, 2005Report This Post
Virtuoso
posted Hide Post
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


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Platinum Member
posted Hide Post
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!


prod: WF 7.7.03 platform IIS on Windows 2007, databases: Oracle, , MSSQL

 
Posts: 133 | Location: Orlando, FL | Registered: August 04, 2005Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Automatically grabbing previous years data

Copyright © 1996-2020 Information Builders