Hy, I have a report which data source is the union of two queries with different date.

On this report I display some amounts. Now I need to show also the difference between them.

Something like this is what I have:

ACROSS FIELD
TYPE 1 TYPE 2 TYPE 3 ...
BY1 DATE1 AMOUNT1 AMOUNT2 AMOUNT3 ...
DATE2 AMOUNT1 AMOUNT2 AMOUNT3 ...
BY2 DATE1 AMOUNT1 AMOUNT2 AMOUNT3 ...
DATE2 AMOUNT1 AMOUNT2 AMOUNT3 ...
. . . . . ...
. . . . . ...
. . . . . ...


And I need something like this:

ACROSS FIELD
TYPE 1 TYPE 2 TYPE 3 ...
BY1 DATE1 AMOUNT1 AMOUNT2 AMOUNT3 ...
DATE2 AMOUNT1 AMOUNT2 AMOUNT3 ...
Difference: And here I'd display the difference of the two above values.
BY2 DATE1 AMOUNT1 AMOUNT2 AMOUNT3 ...
DATE2 AMOUNT1 AMOUNT2 AMOUNT3 ...
Difference: And here I'd display the difference of the two above values.
. . . . . ...
. . . . . ...
. . . . . ...

Any help, greatly appreciated.

This message has been edited. Last edited by: <José Andrés Vargas Aguilar>,

Use RECAP to do the calculation and SUBFOOT to display the values.

Thanks, can you give me an example?

Search the forum for RECAP. I'm confident you'll find plenty of examples.

Creating Reports with WebFOCUS Language is also an excellent reference (search the technical documentation in IBI Web Wite).

Hy, recap can be usefull when calculating custom sums or percentages between columns.

But on this case I need to calculate the difference (that is A - B) between two rows.
I know that on my By I'll always have two dates, not more. And I need to show amount_of_date_1 - amount_of_date_2.

You might be able to use the LAST function to refer to the date of the preceeding row, i.e. DATE - LAST DATE.

Here I illustrate it better this time:

             ACROSS FIELD
          TYPE 1  TYPE 2  TYPE 3  ...
BY1 DATE1 AMOUNT1 AMOUNT2 AMOUNT3 ...
    DATE2 AMOUNT1 AMOUNT2 AMOUNT3 ...
BY2 DATE1 AMOUNT1 AMOUNT2 AMOUNT3 ...
    DATE2 AMOUNT1 AMOUNT2 AMOUNT3 ...
 .    .      .       .       .    ...
 .    .      .       .       .    ...
 .    .      .       .       .    ...

And I need something like this:

             ACROSS FIELD
          TYPE 1  TYPE 2  TYPE 3  ...
BY1 DATE1 AMOUNT1 AMOUNT2 AMOUNT3 ...
    DATE2 AMOUNT1 AMOUNT2 AMOUNT3 ...
Difference: And here I'd display the difference of the two above values.
BY2 DATE1 AMOUNT1 AMOUNT2 AMOUNT3 ...
    DATE2 AMOUNT1 AMOUNT2 AMOUNT3 ...
Difference: And here I'd display the difference of the two above values.
 .    .      .       .       .    ...
 .    .      .       .       .    ...
 .    .      .       .       .    ...

Basic WF doesn't do inter-row calculations very well unless you use FML (lots of examples of that on the forum and in the manuals.)

However, what you're showing doesn't seem to require that. You can make whatever calculation you want and just put them in a subfoot. Using Ginny's suggestion of the LAST function calculate amount - last amount and then show it in a subfoot on the next line.

Excuse me guys I'm pretty new and still not too familiarized with webfocus.

I've found examples of the LAST command being used in DEFINE's and then being printed with the rest of values.

I'd only need to print it once every each BY.
How do I do that?
Where do I use the LAST?

Thanks.

José Andrés, try this:

DEFINE FILE CAR
SEAT_BUCKET/A1 = IF SEATS GE 4 THEN 'B' ELSE 'A';
END
TABLE FILE CAR
SUM
        DEALER_COST
        RETAIL_COST
COMPUTE DEALER_COST_A/D10 = IF SEAT_BUCKET EQ 'A' THEN DEALER_COST ELSE 0; NOPRINT
COMPUTE RETAIL_COST_A/D10 = IF SEAT_BUCKET EQ 'A' THEN RETAIL_COST ELSE 0; NOPRINT
COMPUTE DEALER_COST_B/D10 = IF SEAT_BUCKET EQ 'B' THEN DEALER_COST ELSE 0; NOPRINT
COMPUTE RETAIL_COST_B/D10 = IF SEAT_BUCKET EQ 'B' THEN RETAIL_COST ELSE 0; NOPRINT
BY COUNTRY
BY SEAT_BUCKET NOPRINT
ON COUNTRY RECAP
DEALER_DIFF/D12 = DEALER_COST_B - DEALER_COST_A;
RETAIL_DIFF/D12 = RETAIL_COST_B - RETAIL_COST_A;
ON COUNTRY SUBFOOT
"Difference <DEALER_DIFF<RETAIL_DIFF"
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET STYLE *
TYPE=SUBFOOT, BY=COUNTRY, HEADALIGN=BODY, JUSTIFY=RIGHT, $
END

It'll hopefully give you some ideas you can apply to your particular need.

Thanks nijsden.

Although your solution didn't work for me it got me thinking on how to make it with FML.

And I managed to make it.

After all my accross's and BY's y just added some FML for the 2 rows of dates (dates I know 'cause they're part of the parameters of the report).

FOR FECHATEXTO
'&wf_fchref1' AS '&wf_fchref1' LABEL R1 OVER
'&wf_fchref2' AS '&wf_fchref2' LABEL R2 OVER
BAR AS '-' OVER
RECAP R4 = R1 - R2; AS 'Diferencia' OVER
BAR AS '='

Where FECHATEXTO is my date field.

Thanks a lot to all of you!

Well, I'm glad you found yet another way to skin the cat