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

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

-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

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

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