Going back to the basics, I am sure. Using the car file...I want a report that is This Months Sales and Previous Months sales by Country by Car.
Can I do this in one pass of the data, or do I have to create two hold files and bring them together.
I got SO involved in language translations coding I can't get my arms around this one either. Or it may be all the rain and lack of sun here in Atlanta.

Thanks

Wendy

This message has been edited. Last edited by: MonkeyP,

Create new date variable like BegofPrevMon, EndofPrevMon, BegofCurMon, EndofCurMon.

Now in your Define statment issue something like
prev_sales/D20 = if (sale_date GE BegofPrevMon and sales_date LT EndofPrevMon) then SALES else 0 ;

Cur_sales/D20 = if (sale_date GE BegofCurMon and sales_date LT EndofCurMon) then SALES else 0 ;

In the report use SUM verb.

This example illustrates Rao's idea:

-DEFAULT &PM_FROM = 19970101;
-DEFAULT &PM_TO   = 19970131;
-DEFAULT &CM_FROM = 19970201;
-DEFAULT &CM_TO   = 19970228;

DEFINE FILE GGSALES
PM_SALES/D12 = IF GGSALES.SALES01.DATE FROM &PM_FROM TO &PM_TO THEN GGSALES.SALES01.UNITS ELSE 0;
CM_SALES/D12 = IF GGSALES.SALES01.DATE FROM &CM_FROM TO &CM_TO THEN GGSALES.SALES01.UNITS ELSE 0;
END

TABLE FILE GGSALES
SUM 
     PM_SALES AS 'Prev. Month,Sales'
     CM_SALES AS 'Curr. Month,Sales'
BY  LOWEST GGSALES.SALES01.REGION
WHERE ( GGSALES.SALES01.DATE GE &PM_FROM ) AND ( GGSALES.SALES01.DATE LE &CM_TO );
ON TABLE SET PAGE-NUM NOLEAD 
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
END

Why do people hard-code? Dates change, daily. I know, I've seen them do it!!!

Many ways to do it dynamically, here's 1:

  
-SET &CUR_YR   = &DATEYY;
-SET &CUR_MO   = EDIT(&YYMD,'$$$$99');
-SET &PREV_YR  = IF &CUR_MO  EQ '01' THEN &CUR_YR - 1    ELSE &CUR_YR;
-SET &PREV_MO  = IF &CUR_MO  EQ '01' THEN '12'           ELSE &CUR_MO - 1;
-SET &PREV_MO  = IF &PREV_MO LT 10   THEN '0' | &PREV_MO ELSE &PREV_MO;
-SET &PREV_YYM = &PREV_YR | &PREV_MO;
-TYPE &PREV_YYM

This message has been edited. Last edited by: Tom Flynn,

GGSALES contains static data, it does not change daily as real sources do. I want to provide a quick example that runs as is and hard-coding the values to match those of GGSALES was the fastest way to get to it.

Thanks for teaching us how to dynamically calculate dates though.

Thanks everyone! I think the dynamic solution fits best since our data is updated everyday but the report timeframe is always this month and last month..sometimes this month, last month, and month before that.

Worked like a charm!

Wendy