We want to create a report that looks like this:

             Apr 23 Apr 24 Apr 25 ...
Text1        1000      
Text2                    5     30
             -------------------------
Rolling Sub1  1000      995    965 ...

Text3         2000      
Text4                   100      
             -------------------------
Rolling Sub2  2000     1900   1900 ...

The dates are values in a field of a database so an across looks promising. The Text comes from a field as well. It the rows beginning with Rolling Sub that is the problem.

As in the example they take the first row and subtract the second row. As well the subtraction has to keep rolling on. In other words, my numbers get smaller and smaller.

Any ideas?

Fernando

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

FRL

FML, as of today, anyway!!!

Unfortunately, I "do" remember FRL...

Ok FML to do the row subtraction.
How would I do the rolling subtraction?

In other words How do I code this?

row3 column x = row 1 column 1 - Sum (row 2 column [1 - x])

Fernando

Fernando,

If you don't know FML, it may take awhile. If you want to teach yourself, when you open up a report in the GUI, there is a MATRIX button, that is FML from the GUI. You can also hand-code, but, learn the GUI first.

The main thing is to have get your data to an aggregated level, this is key. Then, you identify data via ROW labels, i.e., R100, R101, etc.

Example of GUI outputed code(snippet):

 
TABLE FILE SPECIAL_FML
SUM
     MTDMSN
     MTDMSO
     MTDAFD
     MTDCCD
     MTDGUD
     MTDMSD
     MTDPPD
     MTDVID
FOR
     CATEGORY
'BILLDAYS'           AS 'Billing Days'       LABEL R1  OVER
'UNITS'              AS 'Actual Units'       LABEL R2  OVER
'UAVG'               AS 'Average Units'      LABEL R3  OVER
'UASS'               AS 'Assigned units'     LABEL R4  OVER
'UAVL'               AS 'Available Units'    LABEL R5  OVER
'MILT'               AS 'Company Miles'      LABEL R6  OVER
" "                  AS " "                  LABEL R7  OVER
'Driver Wages'       AS 'Driver Wages'       LABEL R8  OVER
'Driver Mileage Pay' AS 'Driver Mileage Pay' LABEL R9  OVER
'Driver Per Diem'    AS 'Driver Per Diem'    LABEL R10 OVER
'BAR'                AS 'BAR'                LABEL R11 OVER
     RECAP R12/P16.3BC=R9+R10;  AS 'Total Driver Mileage Pay' OVER

The above data is aggregated up to the CATEGORY level, so, for all the columns that you are SUMming,the RECAP will add R9+R10
and place the totals in R12, for each column. This creates a SUMMARY row.

You'll need to read up on FML, but, once you get going, it is a lot of fun!!!

HAVE FUN!!!

Tom

Another way would be to do the total line separately with a couple of phony sort fields to put the data in order. You can use a COMPUTE to calculate the new field. Then use the MORE, i.e. Universal Calculation to put the files together.

DEFINE FILE filename
SORTFLD/I1=2;
END
TABLE FILE filename
SUM
COMPUTE TEXTCNTR/I9=IF DATE NE LAST DATE THEN TEXTNUM ELSE LAST TEXTCNTR - TEXTNUM; AS TEXTNUM
BY SORTFLD
BY DATE
BY TEXTSORT
ON TABLE HOLD AS TXTHOLD2 FORMAT ALPHA
END
DEFINE FILE TEXTFILE
SORTFLD/I1=1;
END
TABLE FILE TEXTFILE
SUM TEXTNUM
BY SORTFLD
BY TEXTSORT
ACROSS DATE
MORE
FILE TXTHOLD2
END

You'll have to play with this a bit. Not sure of the sort order for the first hold but it will be apparent to you. Also look up MORE to get the restrictions on it as far as column names and formats as such.

way back I posted in the thread http://forums.informationbuild...561096512#7561096512

a method of using compute do a rolling average. The same idea should work for a rolling subtraction.