[CLOSED] Rolling Five-Year Table with Small-Cell Protection

Everyone,

Earlier I created (with help from the forum) a table that created a look back at the previous five years, depending on the year selected. So, if someone selected 2017, the user would see 2017 plus data from the previous five years all the way back to 2012. It works very nicely, and I appreciate all the help I received from other users. That post is: Rolling Five_year Percentage of Change, and MartinY provided a lot of great ideas there.

I'm hoping someone might have an idea related to my current challenge.

I need to create a small-cell version of a table with a rolling five year look back, meaning I have to mask certain values.

I don't have access to the CAR file, but here is a sanitized version of the main code thus far, along with the small-cell fex I am tasked with including in the procedure. I think the key lies in the small-cell procedure as it appears to be set up to create totals by rows not by columns. I need the totals by columns and not rows.

The main code is:

 -SET &HEAD = IF &PARK_NAME EQ '_FOC_NULL' THEN 'All Parks' ELSE &PARK_NAME;

-* EXTRACT SOURCE DATA
DEFINE FILE TICKETS
SALESET/I4 = EDIT(SALES_YEAR);
YR /YY = SALESET
END

-SET &MAXYR = &YR;
-SET &MINYR = &MAXYR-5;

TABLE FILE TICKETS
SUM
SUM.TICKETS.TICKETS.NUMBER
BY  TICKETS.TICKETS.HOLDING_COMPANY
BY TICKETS.TICKETS.PARK_NAME
BY TICKETS.TICKETS.YR
BY  LOWEST TICKETS.TICKETS.PRICE_RANGE
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
WHERE ( TICKETS.TICKETS.REGION_NAME EQ '®ION_NAME.(<Southwest,Southwest.Region:.' );
WHERE ( TICKETS.TICKETS.PARK_NAME EQ '&PARK_NAME.(FIND TICKETS.TICKETS.PARK_NAME IN TICKETS).Park:.' );
WHERE ( TICKETS.TICKETS.YR GE &MINYR);
WHERE ( TICKETS.TICKETS.YR LE &MAXYR);
ON TABLE HOLD AS EXTDATA FORMAT FOCUS
END
-RUN

* EXTRACT MAXIMUM YEAR IN A VARIABLE
-DEFAULTH &YRMAX = 0
TABLE FILE EXTDATA
SUM MAX.YR/I11 AS 'YRMAX'
ON TABLE HOLD AS MAXYR FORMAT BINARY
END
-RUN
-READFILE MAXYR
-SET &YRMAX = &YRMAX;
-TYPE &YRMAX

-* EXTRACT ALL INDIVIDUAL YEARS IN A VARIABLE
-DEFAULTH &EXTYR = 0
TABLE FILE EXTDATA
BY YR/I11 AS 'EXTYR'
ON TABLE HOLD AS YRLIST FORMAT BINARY
END
-RUN

-SET &NBYR = &LINES;

-REPEAT READYR FOR &I FROM 1 TO &NBYR STEP 1
-DEFAULTH &YR.&I = 0
-READFILE YRLIST
-SET &YR.&I = &EXTYR;
-TYPE &YR.&I
-READYR

TABLE FILE EXTDATA
SUM
NUMBER AS 'SC_COUNTS'
BY  HOLDING_COMPANY
BY YR
BY  PRICE_RANGE
ON TABLE SET ASNAMES ON
ON TABLE HOLD AS REPINPUT FORMAT FOCUS
ON TABLE SET STYLE *
$
ENDSTYLE
END
-RUN



-* SET values for small protect processing and -INCLUDE.
-* Some choices are optional.
-SET &input   = 'repinput';
-SET &output  = 'finaldata';
-SET &dim1    = 'HOLDING_COMPANY';
-SET &dim1_id = 'PRICE_RANGE';
-SET &dim2    = 'YR';
-SET &measure = 'SC_COUNTS';
-INCLUDE small_cell_protection.fex

-*Create report based on small protect output.

TABLE FILE finaldata

ON TABLE SUBHEAD
"Julie's Amusement Parks"
"Number of Tickets Sold by Price Range"
"Sales Years &MINYR - &MAXYR"
"&HEAD"
ON TABLE SUBFOOT
"Source: Sales Data         "

    SUM &measure
	 BY &dim1._SORT NOPRINT
	 BY &dim1      AS 'HOLDING_COMPANY' NOPRINT
	 BY LOWEST &dim1_id   AS 'Price Range'
 ACROSS &dim2._SORT NOPRINT
 ACROSS &dim2      AS 'Sales Year'
 WHERE &dim1_id NE 'Total' 
ON &dim1  SUBFOOT
""

ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT &WFFMT.(<HTML Web Document (HTML),HTML>,<Excel XLSX (XLSX),XLSX>).Output type.
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
 $
ENDSTYLE
END
-RUN

Here is the small-cell fex I have to include or rewrite somehow:

  -*Small Cell Protection
-*-----------------------------------------------------------------------------------------------
-* Phase 0 - Initialize
-* Establish default values. This defaulting syntax requires calling procedure to -SET
-* to override the default parameters.
-DEFAULTH &small   = '5';
-DEFAULTH &tsmall  = '3';
-DEFAULT &input   = 'input';
-DEFAULT &output  = 'output';
-DEFAULT &dim1    = ' ';
-DEFAULT &dim1_id = '&dim1.EVAL';
-DEFAULT &dim2    = ' ';
-DEFAULTH &dim2_id = '&dim2.EVAL';
-DEFAULT &measure = ' ';

 SET ASNAME=ON,HOLDLIST=PRINTONLY
 FILEDEF small_cell_data DISK small_cell_data.ftm (APPEND

 DEFINE FILE &input.EVAL
 DIM1_ID/A100 = &dim1_id.EVAL;
 DIM1/A100    = &dim1.EVAL;
 DIM2_ID/I11 = &dim2_id.EVAL;
 DIM2/I11    = &dim2.EVAL;
 MEASURE/D20  = &measure.EVAL;
 END

-*-----------------------------------------------------------------------------------------------
-* Phase 1 - Dim Orders
-* Assign a number to each distinct dimension value and append a Total entry with the last value.
-* This is used for sorting the rows and columns in the final report, including the totals which
-* are calculated in the reporting data (not doing ROW-TOTAL and COLUMN-TOTAL features).
 TABLE FILE &input.EVAL
 SUM     DIM1 NOPRINT
 COMPUTE DIM1_SORT/I11 = IF LAST DIM1 EQ DIM1 THEN DIM1_SORT ELSE DIM1_SORT + 1;
      BY DIM1
 ON TABLE HOLD AS dim1 FORMAT FOCUS INDEX DIM1
 END
 SQL INSERT INTO dim1 VALUES ( 'Total' , 999999999 );
 END

 TABLE FILE &input.EVAL
 SUM     DIM2 NOPRINT
 COMPUTE DIM2_SORT/I11 = IF LAST DIM2 EQ DIM2 THEN DIM2_SORT ELSE DIM2_SORT + 1;
      BY DIM2
 ON TABLE HOLD AS dim2 FORMAT FOCUS INDEX DIM2
 END
 SQL INSERT INTO dim2 VALUES ( 'Total' , 999999999 );
 END

-*-----------------------------------------------------------------------------------------------
-* Phase 2 - Main Data
-* Define a counter SMALL_COUNTER for small values. Define NONSMALL_TOTAL for summing all non-small values.
 DEFINE FILE &input.EVAL ADD
 SMALL_COUNTER/I11  = IF MEASURE LT &small THEN 1       ELSE 0;
 NONSMALL_TOTAL/I11 = IF MEASURE GE &small THEN MEASURE ELSE 0;
 END

 TABLE FILE &input.EVAL
 PRINT   MEASURE
         SMALL_COUNTER
         NONSMALL_TOTAL
 COMPUTE DETAIL/A1 = 'D';
      BY DIM1_ID
      BY DIM1
      BY DIM2_ID
      BY DIM2
 ON TABLE HOLD AS small_cell_data FORMAT ALPHA
 END

-*-----------------------------------------------------------------------------------------------
-* Phase 3 - Row/Col Totals
-* Calculate totals for both dimensions (what will become totals row and column in the final
-* report) and append to previous hold file.
 TABLE FILE &input.EVAL
 SUM     DIM1_ID
         DIM1
 COMPUTE DIM2_ID/I11 = ' ';
 COMPUTE DIM2/I11   = 'Total';
         MEASURE
		 SMALL_COUNTER
         NONSMALL_TOTAL
 COMPUTE DETAIL/A1 = 'T';
      BY DIM1    NOPRINT
      BY DIM1_ID NOPRINT
 ON TABLE HOLD AS small_cell_data FORMAT ALPHA
 END


 TABLE FILE &input.EVAL
 SUM
 COMPUTE DIM1_ID/A100 = ' ';
 COMPUTE DIM1/A100   = 'Total';
         DIM2_ID
         DIM2
         MEASURE
		 SMALL_COUNTER
         NONSMALL_TOTAL
 COMPUTE DETAIL/A1 = 'T';
      BY DIM2    NOPRINT
      BY DIM2_ID NOPRINT
 ON TABLE HOLD AS small_cell_data FORMAT ALPHA
 END

-*-----------------------------------------------------------------------------------------------
-* Phase 4 - Join Sorts
-* Join to dim sort tables to get proper column and row sorting, including Total row and column
-* at the bottom and right.
 JOIN DIM1 IN small_cell_data TO DIM1 IN dim1 AS J1
 JOIN DIM2 IN small_cell_data TO DIM2 IN dim2 AS J2

-*-----------------------------------------------------------------------------------------------
-* Phase 5 - Final Logic
-* Join to dim sort tables to get proper column and row sorting, including Total row and column
-* at the bottom and right.
-SET &ECHO='ALL';
 TABLE FILE small_cell_data
 PRINT
 DIM1           AS '&dim1'
 DIM1_ID        AS '&dim1_id'
 DIM1_SORT      AS '&dim1._SORT'
 DIM2           AS '&dim2'
 DIM2_ID        AS '&dim2_id'
 DIM2_SORT      AS '&dim2._SORT'
 DETAIL
 MEASURE
 SMALL_COUNTER
 NONSMALL_TOTAL
 COMPUTE MEASURE_STR/I11 =
         IF DETAIL EQ 'T' AND SMALL_COUNTER GE 2 AND MEASURE-NONSMALL_TOTAL LE &tsmall THEN NONSMALL_TOTAL ELSE
         IF DETAIL EQ 'T' AND SMALL_COUNTER EQ 1                                       THEN NONSMALL_TOTAL ELSE
		                                                                                    MEASURE;
 COMPUTE MEASURE_END/I11 =
         IF DETAIL EQ 'T' AND SMALL_COUNTER GE 2 AND MEASURE-NONSMALL_TOTAL LE &tsmall THEN MEASURE_STR + &small - 1 ELSE
         IF DETAIL EQ 'T' AND SMALL_COUNTER EQ 1                                       THEN MEASURE_STR + &small - 1 ELSE
                                                                                            0;
-*updated I11 to d11 to shift from integer to decimal in order to display comma formatting
 COMPUTE FMEASURE/A100 =
         IF DETAIL EQ 'D' AND MEASURE LT &small THEN '^'                                                                           ELSE
         IF MEASURE_END EQ 0                    THEN FPRINT(MEASURE_STR, 'D11', 'A40')                                             ELSE
		                                             FPRINT(MEASURE_STR, 'D11', 'A40') || ' -' || FPRINT(MEASURE_END, 'D11', 'A40');
                AS '&measure'
 ON TABLE HOLD AS &output
 END
-RUN

This message has been edited. Last edited by: FP Mod Chuck,

WebFocus 8.2.04
WebFocus 8.2.04

Posts: 191 | Registered: September 18, 2015

BabakNYC

Virtuoso

posted

Hide Post

Is there a simpler way for you to explain the question? It's hard to decipher this code without a master and data. If you look at ../app/ibisamp you should be able to find a sample table that you can use to show us what you'd like help with.

WebFOCUS 8206, Unix, Windows

Posts: 1853 | Location: New York City | Registered: December 30, 2015

Ignored post by BabakNYC posted

Show Post

FP Mod Chuck

Virtuoso

posted

Hide Post

quote:
I think the key lies in the small-cell procedure as it appears to be set up to create totals by rows not by columns. I need the totals by columns and not rows.

I keyed in on this comment and I am probably really oversimplifying but I think you need a
ON TABLE COLUMN-TOTAL instead of NOTOTAL but not sure at what exact point in your code.

Thank you for using Focal Point!

Chuck Wolff - Focal Point Moderator
WebFOCUS 7x and 8x, Windows, Linux All output Formats

Posts: 2127 | Location: Customer Support | Registered: April 12, 2005

Ignored post by FP Mod Chuck posted

Show Post

JulieA

Platinum Member

posted

Hide Post

Here is the result I currently get. You'll notice:

-The year appear to get some sort of counterid. For example, 2010 = 10050 while 10065 and 2012 = 12072.
-The counterid seems to sort correctly, but it places an incorrect counterid on some of the years.
-Our small cell procedure provides a range in cases where only one cell is masked. If more than one cell is masked, then we usually provide the total unmasked.
-The carets are the "mask" in the small cell fex.
-We cannot use the row or column total. Obtaining the totals through the small cell fex allows us to mask and provide ranges when necessary.

I'm trying to figure out how to get the fex to provide the correct counterids and slide the totals below the appropriate sales years. Does that help?

Julie's Amusement Parks												
Number of Tickets Sold by Price Range												
Sales Years 2010 - 2015												
Texas Merriment Company												
												
	Sales Year											
	10050	10055	10065	12072	14098	14105	2010	2011	2012	2013	2014	2015
Ticket Range												
$5 	-	-	-	-	-	-	-	-	-	-	^	^
$6-$20	-	-	-	-	-	-	600	426	536	515	503	551
$21-35	-	-	-	-	-	-	521	591	581	529	634	554
$36-$45	-	-	-	-	-	-	152	161	162	126	152	157
$50-99	-	-	-	-	-	-	106	101	112	99	96	91
$100+	-	-	-	-	-	-	-	-	^	-	^	^
Unknown	-	-	-	-	-	-	^	7	^	^	^	^
 												
	1,374 -1,384	1,286	1,264-1,274	1,391	1397	1380	-	-	-	-	-	-
 												
												
Source: Sales Data												
 												
^ Numbers less than five have been marked for small-cell protection.												
- Cells have been marked to indicate no data.

WebFocus 8.2.04
WebFocus 8.2.04

Posts: 191 | Registered: September 18, 2015

Ignored post by JulieA posted

Show Post

Doug

Expert

posted

Hide Post