Hi all

I have this code:

SUM
ACTUAL_TONN AS 'Actual' OVER
PLAN_TONN AS 'Plan' OVER
RANKED BY TOTAL HIGHEST 10 ACTUAL_TONN NOPRINT AS ' '
BY CUSTOMERNAME AS 'Customer'
ACROSS SALES_MONTH AS ''

and want to put 2 columns with the totals on the far right of the page.
Before you say ROW-TOTAL, there is some additional information.

The total for ACTUAL_TONN does need to be a total for the row,
BUT
The total for the PLAN_TONN needs to be a Year to date (say YTD_PLAN) total. So only add up the amounts for all the months of the year until the current month.

EG
Under PLAN_TONN there is a value of say 100 for each month jan - dec.
But as it is only september, we only want the YTD_PLAN to equal 800. (jan + feb ... + aug).

Now as the ACTUAL_TONN for the current month is still equal to ZERO, I came up with something along the lines of:
YTD_PLAN/D12 = IF ACTUAL_TONN EQ 0 THEN YTD_PLAN ELSE YTD_PLAN + PLAN_TONN;

but I don't know how to make this work, and I don't know how to put this on the far right of my table.

So, 2 columns, both totalling the rows, and both only for the months of the Year-to-date.


here is an attempt at showing you what the table should look like. please try to ignore the .'s they are just spacers.
i hope it comes out alright.


Customer...............Jan . Feb . Mar . Apr . May . Jun . Jul . Aug . Sep . Oct . Nov . Dec . YTD Act . YTD Plan
aaaaaaaa . Actual . 100 . 100 . 100 . 100 . 100 . 100 . 100 .100 . 0 . .. 0 . . 0 . . . 0 . . . .800
................Plan . . .200 . 200 . 200 . 200 . 200 . 200 . 200 .200 . 200 . 200 . 200 . 200 . . . . . . . . 1600
bbbbbbbb

etc


Thanks
Jeremy

Hi Jeremy,

The only way to get your desired result that comes to my mind is by means of the McGyver Technique. You got to multiply your input records and to treat the columns for each of these levels accordingly.
I first put a sample code on the CAR file to show you a possible solution. Afterwards I will put the sample code I always use to get my temporary McGyver table. I use it a lot on similar problems. Like having special totals (as in your report) that are not just the column-total or the row-total or any subtotal.
So here we go:


EX MCGYVER RECS=3
JOIN D_BLK WITH BODYTYPE IN CAR TO ALL MCGKEY IN MCGYVER
DEFINE FILE CAR
D_BLK/A1 WITH BODYTYPE = ' ';
D_DCOST/D7 = IF MCGCNT EQ 1 OR 2
THEN DCOST
ELSE 0;
D_RCOST/D7 = IF MCGCNT EQ 1
THEN RCOST
ELSE IF MCGCNT EQ 3 AND BODYTYPE LT 'SEDAN'
THEN RCOST
ELSE 0;
D_BODYTYPE/A12 = IF MCGCNT EQ 1
THEN BODYTYPE
ELSE IF MCGCNT EQ 2
THEN 'YTD DC'
ELSE 'YTD RC';
D_BODYTSRT/I9 = IF MCGCNT EQ 1
THEN 1
ELSE IF MCGCNT EQ 2
THEN 2
ELSE 3;
END
TABLE FILE CAR
WRITE
D_DCOST AS 'D' OVER
D_RCOST AS 'R'
BY TOTAL TOP DCOST NOPRINT
BY COUNTRY
ACROSS D_BODYTSRT NOPRINT
ACROSS D_BODYTYPE AS ''
END

The first level (MCGCNT eq 1) is your details: the months figures. The second one is your YTD Actual and the third level your YTD Plan. So for each level you got to calculate the appropriate result. I took BODYTYPE instead of your months.
You will have to play around a little bit because of the MONTHS date format to get the correct column titles, but I am sure it will work.
Here now the code for mcgyver.fex:


USE REPLACE
MCGYVER AS MCGYVER NEW
END
-RUN
-*
CREATE FILE MCGYVER
-RUN
MODIFY FILE MCGYVER
COMPUTE
C_CNT/I9 = 0;
-*
REPEAT &RECS TIMES NOHOLD
COMPUTE
C_CNT = C_CNT + 1;
MCGKEY = ' ';
MCGCNT = C_CNT;
-*
MATCH MCGKEY MCGCNT
ON MATCH GOTO ENDREPEAT
ON NOMATCH INCLUDE
ON NOMATCH GOTO ENDREPEAT
ENDREPEAT
GOTO EXIT
-*
DATA VIA FIDEL
END
-RUN

And this is the mcgyver.mas
FILENAME=MCGYVER, SUFFIX=FOC,$
$
SEGNAME=MCGYVER, SEGTYPE=S2,$
$
FIELDNAME=MCGKEY, ,FORMAT=A1, INDEX=I,$
FIELDNAME=MCGCNT, ,FORMAT=I9,$

I hope I am not too late and that my reply is of any help to you !

Cheers

This message has been edited. Last edited by: <Mabel>,

Jeremy,

I did something very similar last week. Here is what I did:

DEFINE FILE (NAME)
YTD_PLAN/D12 = IF ACTUAL_TONN EQ 0 THEN YTD_PLAN ELSE YTD_PLAN + PLAN_TONN;
ACTUALTOT/D12 = ACTUAL_TONN + PLAN_TONN;
END

TABLE FILE (NAME)
SUM
YTD_PLAN
ACTUALTOT
AND COMPUTE SORTIT/A1 = 'B';
BY CUSTOMERNAME (I'm not sure what your BYs should be here)
ON TABLE HOLD AS HOLD1
END

TABLE FILE (NAME)
SUM
ACTUAL_TONN
PLAN_TONN
AND COMPUTE SORTIT/A1 = 'A';
BY TOTAL HIGHEST 10 ACTUAL_TONN
BY CUSTOMERNAME
BY SALES_MONTH
(You'll need to adjust your BYs if necessary and include WHERE statements if needed)
ON TABLE HOLD AS HOLD2
END


MATCH FILE HOLD1
PRINT
YTD_PLAN
ACTUALTOT
CUSTOMERNAME
BY SORTIT
RUN

FILE HOLD2
PRINT
ACTUAL_TONN
PLAN_TONN
BY SORTIT
BY ACTUAL_TONN
BY CUMSTOMERNAME
BY SALES_MONTH
AFTER MATCH HOLD OLD-OR-NEW
END
-RUN


DEFINE FILE HOLD
ACTUALTONN = IF SORTIT EQ 'A' THEN ACTUAL_TONN ELSE IF SORTIT EQ 'B' THEN ACTUALTOT ELSE 0;
PLANTONN = IF SORTIT EQ 'A' THEN PLAN_TONN ELSE IF SORTIT EQ 'B' THEN YTD_PLAN ELSE 0;
MONTH/?? = IF SORTIT EQ 'A' THEN SALES_MONTH ELSE IF SORTIT EQ 'B' THEN 'Total' ELSE '';
END


TABLE FILE HOLD
SUM
ACTUALTONN
PLANTONN
BY SORTIT (You may or may not need this here).
......followed by the rest of your code.


I used this for a turnover report where I had the same issue of needed to only make the total up to a certain month. Two were straight totals, one was an average and one was an ave percent.

Good luck.

Email me if this doesn't explain it enough.

Thanks both of you for your input.

I have been bombed with an urgent project at the moment and this is something that is going to have to wait.

Victoria, from looking at your code, I think it will work with a few adjustments and when I get back to doing particular report, I will post my solution.

Roland, thanks for that, I have heard about the McG technique, but apparently it is very confusing. I'm only new to wF (about 4 weeks, learning everything on the job) so may try it out at a later date.

Again, thanks very much guys
Jeremy