Hello,

We have a report where we display Sales By Account By Day Across Year (or sometimes quarter), for the last three years (or quarters). We only want to see a Daily Running Total for the current year.

I was wondering if it is possible to suppress a Column in one set of an Across group of columns and display it in another.

The closest example I can come up with is from the FINANCE sample file. It does not have daily sales, but the thought is the same.

TABLE FILE FINANCE
PRINT
'FINANCE.TOP.AMOUNT' AS 'Amount'
COMPUTE RUNNINGTOT/D18 = RUNNINGTOT + FINANCE.TOP.AMOUNT; AS 'Running Total'
BY 'FINANCE.TOP.ACCOUNT'
ACROSS 'FINANCE.TOP.YEAR'
HEADING
""
FOOTING
""
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
END


In this example, we would just want to see the RUNNING TOTAL for the year 1983, and suppress the one for 1982.

The reason we are wanting to use the ACROSS phrase is to have the CENTERED title across the groups of columns.

Any thoughts are greatly appreciated.

Well, this isn't pretty and you'll have to play around with the heading to get the years to line up over the right set of columns, but hopefully it will get you started.

TABLE FILE FINANCE
PRINT DST.YEAR
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE SAVE
END
-RUN
-SET &YEARS=&LINES;
SET ASNAMES=ON
TABLE FILE FINANCE
SUM
'FINANCE.TOP.AMOUNT' 
COMPUTE RUNNINGTOT/D18 = RUNNINGTOT + FINANCE.TOP.AMOUNT; 
BY 'FINANCE.TOP.ACCOUNT'
ACROSS 'FINANCE.TOP.YEAR'
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD FORMAT ALPHA
END
-RUN
? HOLD HOLD
TABLE FILE HOLD
BY ACCOUNT
PRINT
-REPEAT ENDLOOP FOR &I FROM 1 TO &YEARS STEP 1
-READ SAVE &RUNYR.A4.
-IF &I LT &YEARS GOTO RUNTOT;
AMO&RUNYR AS 'Amount'
-RUNTOT
RUN&RUNYR AS '&RUNYR,Running Total'
-ENDLOOP
ON TABLE PCHOLD FORMAT HTML
END

I believe you wanted daily *activity* for all years, with *running total* only for the latest year. The code below does that, producing the running total as an ACROSS COMPUTE. I resorted to D.M. only for cosmetics (to capture the year for inclusion in the running total column-title).

APP PREPENDPATH IBIDEMO
DEFINE FILE CENTURYSALES
  Acct/A1=EDIT(SALESREP,'9');
  date/A8MDYY=ORDERDATE;
  Day/A5=EDIT(date,'99/99');
  sample/I1=(MONTH EQ 3) AND (Acct EQ 'A' OR 'B');
END

TABLE FILE CENTURYSALES
  WRITE MAX.YEAR AS CYEAR
  SUM QUANTITY AND COMPUTE CQUANTITY/I8C=IF (YEAR EQ MAX.YEAR) THEN QUANTITY ELSE 0;
    BY Acct BY YEAR BY Day
ON TABLE SET ASNAMES ON
ON TABLE HOLD FORMAT ALPHA
IF sample IS TRUE
END
-RUN
-READ HOLD &CYEAR.A4

TABLE FILE HOLD
  SUM CQUANTITY NOPRINT
  AND COMPUTE CBALANCE/I8C = IF (Acct EQ LAST Acct) THEN  CQUANTITY + LAST CBALANCE ELSE CQUANTITY ; NOPRINT
    BY Acct BY Day
  SUM QUANTITY AS Activity
    BY Acct BY Day
    ACROSS YEAR AS ''
      COMPUTE BALANCE/I8C = CBALANCE ; AS '&CYEAR Running Total'
END

Would this do the trick?

DEFINE FILE FINANCE
RUNNINGTOT/D12 =IF YEAR EQ '1983' THEN AMOUNT + RUNNINGTOT ;
END

TABLE FILE FINANCE
SUM
AMOUNT AS 'Amount'
RUNNINGTOT NOPRINT
BY ACCOUNT
ACROSS YEAR COMPUTE TST/D12 =C4 ; AS 'Runningcost'
HEADING
""
FOOTING
""
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
END


You only need to get the current year with a parameter and the current year needs to be on a fixed place (so for your 3 years it would be C6)

Frans -
Unless records are returned from the data source sorted by account and year, the define will give incorrect results for the running total; and if there is data reduction (multiple records per account and year), the value SUM produces for RunningTotal will be inaccurate. For both those reasons I used Compute rather than Define.
-jg

True,

My way would need aggregated and sorted data back from the database, which you could get with good SQL.