Hello!

I have a project where the user would like to have three different time periods as columns in a single table. Does anyone have an idea how to do this?

I am assuming I will need three separate hold files and then a match statement but that hasn't been working for me so far.

The table would like something like this:

Previous Year | Current Year | Current Month
8 3 1
3 4 2
5 1 5
1 2 1

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

If you have your data properly stored by date you can do the following

DEFINE FILE GGSALES
YEAR /YY = DATE;
MONTH /Mt = DATE;
END
TABLE FILE GGSALES
SUM DOLLARS
BY PRODUCT
ACROSS YEAR
ACROSS MONTH
END

Hey Martin,

Thank you very much for responding to me so quickly!

I thought that method would work as well but then I get an error message when I try to put the two dates in two different Across statements.

Error message is: A column has been specified more than once in the order by list.

Brandon

Post your code so we can see it. From the sound of the error message your field name must be duplicated.

Hey Chuck,

Here is the code that I am working with

DEFINE FILE _DST_STAR
PREV_YEAR/YYMD = _DST_STAR.DST_DIMDATE.DATECALENDARKEY;
CUR_YEAR/YYMD = _DST_STAR.DST_DIMDATE.DATECALENDARKEY;
END
TABLE FILE _DST_STAR
SUM
NUM
BY MASTERIPA
ACROSS PREV_YEAR
ACROSS CUR_YEAR
END
-EXIT

I'm not sure to understand when you say that you need to put the two dates into two ACROSS.
The ACROSS verb is similar to a BY, but it's become a "BY Column" instead of "By Row" as a regular BY field.
Think of Excel Pivot table. Rows = BY, Column = ACROSS, Values = SUM/PRINT

You may have an issue with your data source and/or structure.

It may be a good idea to share your code and data structure (master file).
To do so, use the code tag. Last one on the ribbon

</>

Depending of your data structure you may not need three separate hold files to become one. Can't say if it's going to be or not the case.

What is exactly your Previous year, Current Year & Current month ?
What is the difference between Current Year and Current Month ? Is Current Year all from Jan up to now where Current Month it's only Oct 2018 ?

No need to have two columns defined

DEFINE FILE _DST_STAR
-*PREV_YEAR/YYMD = _DST_STAR.DST_DIMDATE.DATECALENDARKEY;
-*CUR_YEAR/YYMD = _DST_STAR.DST_DIMDATE.DATECALENDARKEY;
YEAR/YYMD = _DST_STAR.DST_DIMDATE.DATECALENDARKEY;
END
TABLE FILE _DST_STAR
SUM NUM
BY MASTERIPA
-*ACROSS PREV_YEAR
-*ACROSS CUR_YEAR
ACROSS YEAR
END
-EXIT

Brandon

The way you have done your DEFINE you are referencing the same field and causing the underlying error you see


PREV_YEAR/YYMD = _DST_STAR.DST_DIMDATE.DATECALENDARKEY;
CUR_YEAR/YYMD = _DST_STAR.DST_DIMDATE.DATECALENDARKEY;


Follow Martin's advice.

Ok,

I'm getting there!

Martin, thank you very much for helping me troubleshoot this!

I am very very close!

So I searched through the forums and found out that Universal Concatenation might be a good bet here. So I took what you said and made three hold files.

I need to identify three time periods using months for each one.

1: Previous Year based on month
2. Current Year: current 12 month period
3. Current YTD: current month

I have this part solved and I think I am good!

This is what I have so far.

 

TABLE FILE _DST_STAR
SUM
     NUM
BY _DST_STAR.DST_DIMDATE.DATECALENDARKEY 
WHERE _DST_STAR.DST_DIMDATE.DATECALENDARKEY  EQ '2017/12/31'
ON TABLE HOLD AS 'PREV_YEAR'
END

TABLE FILE _DST_STAR
SUM
     NUM
BY  _DST_STAR.DST_DIMDATE.DATECALENDARKEY
ON TABLE HOLD AS 'MONTH_TREND'
END

TABLE FILE _DST_STAR
SUM
     NUM
	 ADHERENCE
	 DEN
BY  _DST_STAR.DST_DIMDATE.DATECALENDARKEY
ON TABLE HOLD AS 'YTD_TREND'
END

TABLE FILE PREV_YEAR
HEADING
"Yup Yup"
""
SUM
	NUM
ACROSS DATECALENDARKEY
MORE
FILE MONTH_TREND
MORE
FILE YTD_TREND
END
-EXIT

 

However, there are two additional things I want to do here:

1. Apply individual heading names to each of the three groupings
2. Current YTD requires two additional fields that are not available in the other two groupings (Adherence, and DEN)

Does anyone have idea of how to address these two obstacles?

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

I don't know you field format so I assume a format.
Something such as this may do the trick to answer Q2

TABLE FILE _DST_STAR
SUM NUM
    COMPUTE ADHERENCE /P8 MISSING ON = MISSING;
    COMPUTE DEN       /P8 MISSING ON = MISSING;
BY _DST_STAR.DST_DIMDATE.DATECALENDARKEY 
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 /P8 MISSING ON = MISSING;
    COMPUTE DEN       /P8 MISSING ON = MISSING;
BY  _DST_STAR.DST_DIMDATE.DATECALENDARKEY
ON TABLE HOLD AS 'MONTH_TREND'
END

TABLE FILE _DST_STAR
SUM NUM
    ADHERENCE
    DEN
BY  _DST_STAR.DST_DIMDATE.DATECALENDARKEY
ON TABLE HOLD AS 'YTD_TREND'
END

TABLE FILE PREV_YEAR
HEADING
"Yup Yup"
""
SUM NUM
    ADHERENCE
    DEN
ACROSS DATECALENDARKEY
MORE
FILE MONTH_TREND
MORE
FILE YTD_TREND
END
-EXIT

Need to think about it to answer Q1

Thank you! I think that is def a step in the right direction.

Do you have an idea of how to NOPRINT the columns with MISSING values? I'd like to not have them display at all.

Brandon

Use a WHERE condition to screen out the MISSING values.

A WHERE clause will not only omit the column with missing values, it will omit the whole data row.

Not the most elegant way but it works

DEFINE FILE GGSALES
YEAR  /YY  = DATE;
MONTH /Mt  = DATE;
END
TABLE FILE GGSALES
SUM DOLLARS AS 'COLVAL'
BY PRODUCT
BY YEAR
BY MONTH
BY TOTAL COMPUTE COLID  /I1   = 1;
BY TOTAL COMPUTE COLTXT /A10V = 'Sales';
WHERE YEAR EQ '1996';
ON TABLE HOLD AS TMP1
END
-RUN

TABLE FILE GGSALES
SUM DOLLARS AS 'COLVAL'
BY PRODUCT
BY YEAR
BY MONTH
BY TOTAL COMPUTE COLID  /I1   = 1;
BY TOTAL COMPUTE COLTXT /A10V = 'Sales';
WHERE YEAR EQ '1997';
ON TABLE HOLD AS TMP2
END
-RUN

TABLE FILE GGSALES
SUM BUDDOLLARS AS 'COLVAL'
BY PRODUCT
BY YEAR
BY MONTH
BY TOTAL COMPUTE COLID  /I1   = 2;
BY TOTAL COMPUTE COLTXT /A10V = 'Budget';
WHERE YEAR EQ '1997';
ON TABLE HOLD AS TMP3
END
-RUN

TABLE FILE TMP1
SUM COLVAL
BY PRODUCT
BY YEAR
BY MONTH
BY COLID
BY COLTXT
ON TABLE HOLD AS MRGFIL
MORE
FILE TMP2
MORE
FILE TMP3
END
-RUN

TABLE FILE MRGFIL
SUM COLVAL/D8 AS ''
BY PRODUCT    AS 'Product'
ACROSS YEAR   AS ''
ACROSS MONTH  AS ''
ACROSS COLID  NOPRINT
ACROSS COLTXT AS ''
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET HTMLEMBEDIMG ON
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,
$
ENDSTYLE
END
-RUN

Thanks Martin and Chuck for your input!

Martin, wow that works perfectly.

I'm still having difficulty with hiding the other columns that have MISSING values with the WHERE Clause. I have put it in the merged/universal concatenation file but the values are still displaying and it completely omits the first hold file.

 

TABLE FILE _DST_STAR
SUM
     NUM
	 COMPUTE ADHERENCE/D12.2% MISSING ON = MISSING;
	 COMPUTE DEN/I11 MISSING ON = MISSING;
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'
WHERE DEN NE MISSING
ON TABLE HOLD AS 'PREV_YEAR'
END

TABLE FILE _DST_STAR
SUM
     NUM
	 COMPUTE ADHERENCE/D12.2% MISSING ON = MISSING;
	 COMPUTE DEN/I11 MISSING ON = MISSING;
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
ACROSS COLID NOPRINT
ACROSS COLTXT AS ''
ACROSS DATECALENDARKEY AS ''
BY MEASUREFULLNAME
WHERE DEN NE MISSING
-*WHERE ADHERENCE NE MISSING
MORE
FILE MONTH_TREND
MORE
FILE YTD_TREND
END
-EXIT 

Should I be using a different WHERE clause or placing it somewhere differently?

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

quote:

Should I be using a different WHERE clause or placing it somewhere differently?

As I previously stated :

quote:

A WHERE clause will not only omit the column with missing values, it will omit the whole data row.

Look at my last sample code for the technic that I've used.

Hey Martin,

Thanks for the quick reply. Your code definitely makes sense, but I want to display three SUM fields in the last Col ID, and only have one SUM field in the 1st column. Do you have any idea of how to accomplish that?

I'm gonna create a new thread just for this request since it's outside the scope of what I have now accomplished. Thanks for all of your help!!!

quote:

but I want to display three SUM fields in the last Col ID

Same technic, just repeat it for a third one

Martin, it finally clicked!

Holy cow, so cool. Thanks a lot. I will post my code when I get this working 100%

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