Hello,
I have a column for witch I want to totalise only the last value before each subtotal.
Example
Date Arrival QTY Date USED QTY USED STOCK
2013-06-01 50 2013-06-02 10 40
2013-06-03 12 28
Total 2013-06-01 50 22 28
2013-06-10 100 2013-06-11 45 55
2013-06-13 27 28
Total 2013-06-10 100 72 28
TOTAL 150 94 56

How can I do to that ?
If you have any idea, you're welcome.
Thanks.
Catherine

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

there are a number of field prefixes available which can be included in a SUBFOOT and other places.
Example
LST.fieldname [as] .... select the last value only

if you surround your sample table with [ code ] tags, like so...

Date Arrival       QTY   Date USED    QTY USED  STOCK
2013-06-01         50    2013-06-02    10       40
                         2013-06-03    12       28
Total 2013-06-01   50                  22       28
2013-06-10         100   2013-06-11    45       55
                         2013-06-13    27       28
Total 2013-06-10   100                 72       28
TOTAL              150                 94       56

...it makes it much easier for your audience to grasp what you're after.

So you want the subtotal line to summarize the quantity added, the quantities withdrawn, and the net remaining stock.

...
ON DATE_ARRIVAL SUBTOTAL QTY DATE_USED QTY_USED LST. STOCK
...

The only issue I see with trying to use LST. is that it may not work for the final "TOTAL" line where she seems to need a summary of each "last value" within each internal sub-group, and not just the last value among them all.

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

Cati, maybe I am not understanding your request correctly and hopefully using LST. prefix as suggested by John and j.gross will help you as it is a simpler and cleaner approach.

If that is not the case however, and your request is not only to display the last value of a field for certain groups but also adding each of those "last values" and display the total at the end of the report, you may follow this code which attempts to produce exactly that:

-* Sort by TRANS_DATE in descending order to easily capture the "last" record and DEALER_COST for each CAR
DEFINE FILE CAR
TRANS_DATE/A8YYMD = EDIT('&YYMD', '999999' || FPRINT(SEATS, 'I2S', 'A2'));
END

TABLE FILE CAR
PRINT
        RETAIL_COST
        DEALER_COST
COMPUTE LST_IND/I9 = IF CAR NE LAST CAR THEN 1 ELSE 0;
COMPUTE LST_DEALER_COST/D12 = IF CAR NE LAST CAR THEN DEALER_COST ELSE 0;
BY CAR
WHERE CAR LE 'J'
BY HIGHEST TRANS_DATE 
ON TABLE HOLD AS HTRANS
END
-RUN

-* Display results in ascending order by TRANS_DATE and last record indicator, displaying only the last DEALER_COST value
-* in each subfoot by CAR and then at the end of the report

DEFINE FILE HTRANS
TTL/A1 = ' ';
END
TABLE FILE HTRANS
PRINT
        RETAIL_COST
        DEALER_COST
        LST_DEALER_COST  NOPRINT      
BY TTL NOPRINT
BY CAR
BY TRANS_DATE
BY LST_IND  NOPRINT

ON CAR SUBFOOT
"Total<TRANS_DATE<ST.RETAIL_COST<ST.LST_DEALER_COST"

ON TTL SUBFOOT
"TOTAL<+0> <ST.RETAIL_COST<ST.LST_DEALER_COST"

ON TABLE PCHOLD FORMAT HTML
ON TABLE SET STYLE *
TYPE=TITLE, STYLE=BOLD, BACKCOLOR='SILVER', $
$
TYPE=SUBFOOT, BY=CAR, BACKCOLOR=RGB(220 220 220), HEADALIGN=BODY, $
TYPE=SUBFOOT, BY=CAR, OBJECT=FIELD, ITEM=1, JUSTIFY='RIGHT', $
TYPE=SUBFOOT, BY=CAR, OBJECT=FIELD, ITEM=2, JUSTIFY='RIGHT', $
TYPE=SUBFOOT, BY=CAR, OBJECT=FIELD, ITEM=3, JUSTIFY='RIGHT', $
$
TYPE=SUBFOOT, BY=TTL, STYLE=BOLD, BACKCOLOR=RGB(250 240 250), HEADALIGN=BODY, $
TYPE=SUBFOOT, BY=TTL, OBJECT=FIELD, ITEM=1, JUSTIFY='RIGHT', $
TYPE=SUBFOOT, BY=TTL, OBJECT=FIELD, ITEM=2, JUSTIFY='RIGHT', $
ENDSTYLE
END

This is what you would get:




The code may seem like an overkill to what looks like a simple request. Hopefully you'll find an easier way to it

Hope this helps.

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

Thanks everybody for your answers.

I've tried Njsden solution but with SUM instead of PRINT.
For the first Total it's OK with LST function but not for the last total.

I continue to search.
If you have any other idea......

Bye
Catherine

Hi,

What Neftali shared was as per your requirement.

If that is not correct, then provide sample report.

Thanks,
Ram

Cati, then do your TABLE FILE ... SUM to obtain all of the aggregated values BY the groups you need from the database table, temporarily saving those results in a HOLD file; you can then try to play and implement the described technique off of that HOLD file where you can now safely use PRINT.

As Ram suggested, it would absolutely be much better if you created a workable example with any of the IBI-provided files (CAR, GGSALES). That way whoever is able to help can focus on the problem at hand without devoting unnecessary effort to try and replicate your data conditions which only you know

Cati, there are so many prefix operators, it seems like some combination of these should get the result you need.
Prefix list:
ALL.fieldname [as] .... include missing values (parent - no child)
ASQ.fieldname [as] .... compute the average sum of squares
AVE.fieldname .... compute the average value
AVE.DST.fieldname [as] .... compute the average of unique value
CNT.fieldname [as] .... count the items
CNT.DST.fieldname [as] .... count the unique items
FST.fieldname [as] .... select the first value only
LST.fieldname [as] .... select the last value only
MAX.fieldname [as] .... select the maximum value only
MIN.fieldname [as] .... select the minimum value only
PCT.fieldname [as] .... compute the percent of column total
PCT.CNT.fieldname [as] .... percent of a count to total count
RNK.fieldname [as] .... compute the numerical rank within the sort field
RPCT.fieldname [as] .... compute the percent of row total
SEG.fieldname [as] .... all fields in segment of field
SEGMENT fieldname [as] .... all fields in segment of field
SUM.fieldname [as] .... compute sum of values
SUM.DST.fieldname [as] .... sum the unique items
TOT.fieldname [as] .... compute column total
as: AS text .... see Format Control
From the WebFOCUS Keysheet - Reports Chapter