How to get the previous year value in Webfocus?

Hi All,

There is a Fact table with Month and other dimension keys in it. I will be passing a Month filter as a prompt for the report. My report structure is Current_Year_Value,Last_Year_Value and some of the dimension values.

ie. if I give 201308(YYM) as filter, it should retrieve 201308 and 201208 value.

How can I achieve it?. Need your valuable suggestions.

Webfocus 8.0.0.3, Windows Xp

Posts: 28 | Registered: December 28, 2011

Wep5622

Virtuoso

posted

Hide Post

We do something like that using MATCH FILE and converting the previous year to this year, like so:

SET ASNAMES = ON

DEFINE FILE GGSALES
  YEAR2/I4 = YEAR +1;
END
MATCH
 FILE GGSALES
 SUM DOLLARS UNITS
 BY YEAR
 BY CATEGORY
 WHERE YEAR EQ 2013;
RUN
 FILE GGSALES
 SUM DOLLARS UNITS
 BY YEAR2 AS YEAR
 BY CATEGORY
 WHERE YEAR EQ 2012;
AFTER MATCH HOLD AS YADAYADA OLD-OR-NEW
END
-RUN

WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :

Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010

Ignored post by Wep5622 posted

Show Post

Rifaz

Guru

posted

Hide Post

-SET &AnnDt = &DATEYYMD;
-SET &ENDDATE = EDIT(&AnnDt,'9999$$$$$$') || EDIT(&AnnDt,'$$$$$99$$$')||EDIT(&AnnDt,'$$$$$$$$99');
-SET &END_DT = DATETRAN(DATECVT(&ENDDATE, 'I8YYMD', 'MYY'), '(MYY)', '(tc)', 'EN', 20, 'A20') ;
-SET &STARTDATE = DATECVT((DATEADD((DATECVT(&ENDDATE,'I8YYMD','YYMD')),'M', -12)),'YYMD','I8YYMD');
-SET &START_DT = DATETRAN(DATECVT(&STARTDATE, 'I8YYMD', 'MYY'), '(MYY)', '(tc)', 'EN', 20, 'A20') ;
-SET &ENDDATEFMT = EDIT(&STARTDATE,'9999$$$$') ||'/'|| EDIT(&STARTDATE,'$$$$99$$')||'/'|| EDIT(&STARTDATE,'$$$$$$99');
-TYPE &AnnDt &STARTDATE &ENDDATE &ENDDATEFMT &START_DT &END_DT

Always, like to have in my bookmark.

Thanks,
Rifaz

-Rifaz

WebFOCUS 7.7.x and 8.x

Posts: 406 | Location: India | Registered: June 13, 2013

Ignored post by Rifaz posted

Show Post

GamP

Virtuoso

posted

Hide Post

Suppose, &SELDATE is your variable that contains the year/month value, for example 201308.
The following statement will create the begin date:
-SET &STARTDATE = &SELDATE - 100;
It will now contain 201208.
Hope this helps ...

GamP

- Using AS 8.2.01 on Windows 10 - IE11.
	in Focus since 1988

Posts: 1961 | Location: Netherlands | Registered: September 25, 2007

Ignored post by GamP posted

Show Post

Twanette

Platinum Member

posted

Hide Post

This is a great "101-ways" topic ;-)

Assuming your filter is called &MONTH_FILTER, and has a value of '201308', and assuming your date format is YYM:

-SET &A_YEAR_AGO = AYM(&MONTH_FILTER,-12,'I6');

DEFINE FILE Your_DB
  Current_Year_Value/P20 = IF Your_YYM EQ &MONTH_FILTER.QUOTEDSTRING THEN 
                           Your_Value_Field ELSE 0 ;
  Last_Year_Value/P20    = IF Your_YYM EQ &A_YEAR_AGO.QUOTEDSTRING THEN 
                           Your_Value_Field ELSE 0 ;
END

TABLE FILE Your_DB
SUM Current_Year_Value Last_Year_Value
BY Your_Dimensions
IF Your_YYM EQ &MONTH_FILTER.QUOTEDSTRING OR &A_YEAR_AGO.QUOTEDSTRING
END

WebFOCUS 8.2.06 mostly Windows Server

Posts: 195 | Location: Johannesburg, South Africa | Registered: September 13, 2008

Ignored post by Twanette posted

Show Post

Please Wait. Your request is being processed...