Hello!

I am merging three hold files together via the MORE command. They are organized into three date groupings via an ACROSS statement. I only want to display the ADHERENCE and DEN values for the third Date grouping which is COLID 3. Does anyone have an idea of how to do this?

Thanks in advance!!! Code below:

 TABLE FILE _DST_STAR
SUM
     NUM
	 COMPUTE ADHERENCE/D12.2% = NUM/DEN;
	 DEN
BY _DST_STAR.DST_DIMDATE.DATECALENDARKEY
BY _DST_STAR.DST_DIMQUALITYMEASURE.MEASUREFULLNAME
BY TOTAL COMPUTE COLID/I1 = 1;
BY TOTAL COMPUTE COLTXT/A20V = 'Previous Year';
WHERE _DST_STAR.DST_DIMDATE.DATECALENDARKEY  EQ '2017/12/31'
ON TABLE HOLD AS 'PREV_YEAR'
END

TABLE FILE _DST_STAR
SUM
     NUM
	 COMPUTE ADHERENCE/D12.2% = NUM/DEN;
	 DEN

BY  _DST_STAR.DST_DIMDATE.DATECALENDARKEY
BY _DST_STAR.DST_DIMQUALITYMEASURE.MEASUREFULLNAME
BY TOTAL COMPUTE COLID/I1 = 2;
BY TOTAL COMPUTE COLTXT/A20V = 'Monthly Trend';
WHERE _DST_STAR.DST_DIMDATE.DATECALENDARKEY  EQ '2018/04/30'
ON TABLE HOLD AS 'MONTH_TREND'
END

TABLE FILE _DST_STAR
SUM
     NUM
	 COMPUTE ADHERENCE/D12.2% = NUM/DEN;
	 DEN
BY  _DST_STAR.DST_DIMDATE.DATECALENDARKEY
BY _DST_STAR.DST_DIMQUALITYMEASURE.MEASUREFULLNAME
BY TOTAL COMPUTE COLID/I1 = 3;
BY TOTAL COMPUTE COLTXT/A20V = 'Current YTD';
WHERE _DST_STAR.DST_DIMDATE.DATECALENDARKEY  EQ '2018/10/02'
ON TABLE HOLD AS 'YTD_TREND'
END

TABLE FILE PREV_YEAR
HEADING
""
SUM
	NUM
	DEN
	ADHERENCE
BY COLID
BY COLTXT
BY DATECALENDARKEY
BY MEASUREFULLNAME
ON TABLE HOLD AS 'COMB_TREND'
MORE
FILE MONTH_TREND
MORE
FILE YTD_TREND
END

TABLE FILE COMB_TREND
SUM
	NUM
	DEN
	ADHERENCE
ACROSS DATECALENDARKEY AS ''
ACROSS COLID
ACROSS COLTXT AS ''
BY MEASUREFULLNAME
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET HTMLEMBEDIMG ON
ON TABLE SET HTMLCSS ON
END 

This message has been edited. Last edited by: Brandon Andrathy,

Brandon

Maybe I am missing something but in the last secion of your code just don't refrence the NUM field...


TABLE FILE COMB_TREND
SUM
DEN
ADHERENCE
ACROSS DATECALENDARKEY AS ''
ACROSS COLID
ACROSS COLTXT AS ''
BY MEASUREFULLNAME
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET HTMLEMBEDIMG ON
ON TABLE SET HTMLCSS ON
END


Chuck,

What Brandon wants is to have Num only displayed in the first two ACROSS value (PY and Mth Trend) without ADHERENCE and DEN but in the third ACROSS value (YTD) he wants NUM, ADHERENCE and DEN displayed.

I posted a solution in its other discussion.

ACROSS will always display all measures (SUM field) by default. See your other post for a technic how to accomplish your need.

Martin def helped me.

I'm good to go! I'll post my code when I'm done. Really cool. Thanks a lot!!!

This ended up being a work around via clever algebraic field manipulation thanks to Martin.

Here's the code I came up with. I created another variable similar to COLID for the sum fields called SUMID. Then those get organized under each COLID .

 TABLE FILE _DST_STAR
SUM
     COMPUTE COLVAL/D12 = NUM;
BY _DST_STAR.DST_DIMDATE.DATECALENDARKEY
BY _DST_STAR.DST_DIMQUALITYMEASURE.MEASUREFULLNAME
BY TOTAL COMPUTE COLID/I1 = 1;
BY TOTAL COMPUTE SUMID/I1 = 1;
BY TOTAL COMPUTE COLTXT/A20V = 'Previous Year';
WHERE _DST_STAR.DST_DIMDATE.DATECALENDARKEY  EQ '2017/12/31'
ON TABLE HOLD AS 'PREV_YEAR'
END

TABLE FILE _DST_STAR
SUM
     COMPUTE COLVAL/D12 = NUM;
BY  _DST_STAR.DST_DIMDATE.DATECALENDARKEY
BY _DST_STAR.DST_DIMQUALITYMEASURE.MEASUREFULLNAME
BY TOTAL COMPUTE COLID/I1 = 2;
BY TOTAL COMPUTE SUMID/I1 = 2;
BY TOTAL COMPUTE COLTXT/A20V = 'Monthly Trend';
WHERE _DST_STAR.DST_DIMDATE.DATECALENDARKEY  EQ '2018/04/30'
ON TABLE HOLD AS 'MONTH_TREND'
END

TABLE FILE _DST_STAR
SUM
	COMPUTE COLVAL/D12 = NUM;
BY  _DST_STAR.DST_DIMDATE.DATECALENDARKEY
BY _DST_STAR.DST_DIMQUALITYMEASURE.MEASUREFULLNAME
BY TOTAL COMPUTE COLID/I1 = 3;
BY TOTAL COMPUTE SUMID/I1 = 3;
BY TOTAL COMPUTE COLTXT/A20V = 'Current YTD';
WHERE _DST_STAR.DST_DIMDATE.DATECALENDARKEY  EQ '2018/10/02'
ON TABLE HOLD AS 'YTD_TREND_NUM'
END

TABLE FILE _DST_STAR
SUM
	COMPUTE COLVAL/D12 = DEN;
BY  _DST_STAR.DST_DIMDATE.DATECALENDARKEY
BY _DST_STAR.DST_DIMQUALITYMEASURE.MEASUREFULLNAME
BY TOTAL COMPUTE COLID/I1 = 3;
BY TOTAL COMPUTE SUMID/I1 = 4;
BY TOTAL COMPUTE COLTXT/A20V = 'Current YTD';
WHERE _DST_STAR.DST_DIMDATE.DATECALENDARKEY  EQ '2018/10/02'
ON TABLE HOLD AS 'YTD_TREND_DEN'
END

TABLE FILE _DST_STAR
SUM
	COMPUTE COLVAL/D12 = NUM/DEN*100;
BY  _DST_STAR.DST_DIMDATE.DATECALENDARKEY
BY _DST_STAR.DST_DIMQUALITYMEASURE.MEASUREFULLNAME
BY TOTAL COMPUTE COLID/I1 = 3;
BY TOTAL COMPUTE SUMID/I1 = 5;
BY TOTAL COMPUTE COLTXT/A20V = 'Current YTD';
WHERE _DST_STAR.DST_DIMDATE.DATECALENDARKEY  EQ '2018/10/02'
ON TABLE HOLD AS 'YTD_TREND_ADH'
END 

Now all I gotta do is play around with the formatting of the single sum field but I feel comfortable manipulating an Alphanumeric format.

Thanks so much. Didn't think this could be done!!

quote:

SUM
COMPUTE COLVAL/D12 = NUM;

The above may not be required.
You can do the following instead with the same effect but possibly faster (talking internal process)

SUM
     NUM/D12 AS 'COLVAL'

quote:

Didn't think this could be done!!

Almost anything can be done in FOCUS, just need to find the way !!!

Glad to have helped