How do I get RECAP to recalculate COST in the example below? With the code as it is, the two COST values simply get added.

DEFINE FILE CAR
RETAIL_SALES/D10 = SALES * RETAIL_COST;
COST/D10 = SALES * DEALER_COST;
END
TABLE FILE CAR
SUM
RETAIL_SALES
COST
COMPUTE PROFITPCT/D6.1 = (RETAIL_SALES-COST) / RETAIL_SALES * 100;
FOR
COUNTRY
'ENGLAND' LABEL CN1 OVER
'JAPAN'   LABEL CN2 OVER
RECAP TOT = CN1 + CN2; AS 'TOTAL'
END

Thanks.

I did not get anything.

Only Italy is in my database. do I have to update the database?

TABLE FILE CAR
SUM COUNTRY BY COUNTRY NOPRINT
END

gives me

ENGLAND
FRANCE
ITALY
JAPAN
W GERMANY

If you wish, you can run
dblcar.fex in C:\ibi\apps\ggdemo or
loadcar.fex in C:\ibi\apps\ibisamp
to reload the FOCUS DB. You may have to tweak the USE command.

Francis,

Essaie ce code :

SET STYLEMODE = FIXED

-*=======
DEFINE FILE CAR
-*=======
BIDON /A1 = ' ';
RETAIL_SALES/D10 = SALES * RETAIL_COST ;
COST /D10 = SALES * DEALER_COST ;

END

-*=======
TABLE FILE CAR
-*=======
SUM RETAIL_SALES
COST

COMPUTE PROFITPCT/D6.1 = (RETAIL_SALES - COST) / RETAIL_SALES * 100;

BY BIDON NOPRINT

ON BIDON RECOMPUTE AS 'TOTAL'

FOR COUNTRY
'ENGLAND' LABEL CN1 OVER
'JAPAN' LABEL CN2

-*ECAP TOT = CN1 + CN2; AS 'TOTAL'

ON TABLE SET PAGE NOLEAD
ON TABLE NOTOTAL

END
-RUN

Résultat :

RETAIL_SALES COST PROFITPCT
------------ ---- ---------
ENGLAND 161,892,000 134,328,000 17.0
JAPAN 251,942,170 214,014,580 15.1

TOTAL 413,834,170 348,342,580 15.8

Amitié,
Michel

Merci Michel, mais...

My example wasn't clear enough. I need to use FML for the FOR and RECAP commands. Here is a better example:

DEFINE FILE CAR
RETAIL_SALES/D10 = SALES * RETAIL_COST;
COST/D10 = SALES * DEALER_COST;
END
TABLE FILE CAR
SUM
RETAIL_SALES
COST
COMPUTE PROFITPCT/D6.1 = (RETAIL_SALES-COST) / RETAIL_SALES * 100;
FOR
COUNTRY
'W GERMANY' LABEL CNE1 OVER
'ITALY' LABEL CNE2 OVER
RECAP TOTE = CNE1 + CNE2; AS 'TOTAL EUROPE' OVER
'JAPAN'   LABEL CNA1 OVER
'TAIWAN'  LABEL CNA2 OVER
RECAP TOTA = CNA1 + CNA2; AS 'TOTAL ASIA' OVER
RECAP TOT = TOTE + TOTA; AS TOTAL
END

I would like my subtotals and my grand total to recalculate the %.

Thanks.

Michel,

quote:

BY BIDON NOPRINT
ON BIDON RECOMPUTE AS 'TOTAL'

You do not require the additional DEFINE, just use ON TABLE RECOMPUTE.

T

I just don't get why the FML RECAP doesn't recompute the %

Francis,

RECAP in FML doesn't recompute. C'est comme ca. But you can always redo the calculation. See below:

DEFINE FILE CAR
RETAIL_SALES/D10 = SALES * RETAIL_COST;
COST/D10 = SALES * DEALER_COST;
END
TABLE FILE CAR
SUM
RETAIL_SALES
COST
COMPUTE PROFITPCT/D6.1 = (RETAIL_SALES-COST) / RETAIL_SALES * 100;
FOR
COUNTRY
'ENGLAND' LABEL CN1 OVER
'JAPAN' LABEL CN2 OVER
RECAP TOT(1,2) = CN1 + CN2; AS 'TOTAL' OVER
RECAP TOT(3) = (TOT(1) - TOT(2)) / TOT(1) * 100;
END

Regards,

Got it, Danny! Thanks!

Seems bl**dy counter-intuitive. One would think that, if, as described in the manual, FML is some kind of sophisticated financial reporting language, it would automate formula recalculating, since that's what financial reports are all about.

It seems strange that you have to code an OVER when the second RECAP is actually recalculating a cell in the same line as the first RECAP.

DEFINE FILE CAR
RETAIL_SALES/D10 = SALES * RETAIL_COST;
COST/D10 = SALES * DEALER_COST;
END
TABLE FILE CAR
SUM
RETAIL_SALES
COST
COMPUTE PROFITPCT/D6.1 = (RETAIL_SALES-COST) / RETAIL_SALES * 100;
FOR
COUNTRY
'W GERMANY' LABEL CNE1 OVER
'ITALY' LABEL CNE2 OVER
RECAP TOTE = CNE1 + CNE2; AS 'TOTAL EUROPE' OVER
RECAP TOTE(3) = (TOTE(1) - TOTE(2)) / TOTE(1) * 100; OVER

'JAPAN'   LABEL CNA1 OVER
'TAIWAN'  LABEL CNA2 OVER
RECAP TOTA = CNA1 + CNA2; AS 'TOTAL ASIA' OVER
RECAP TOTA(3) = (TOTA(1) - TOTA(2)) / TOTA(1) * 100; OVER

RECAP TOT = TOTE + TOTA; AS 'TOTAL' OVER
RECAP TOT(3) = (TOT(1) - TOT(2)) / TOT(1) * 100;
END

Now to apply this to my real-world report.

Thanks again.

Thanks Francis
I will do the reload and run your latest solution tomorrow.

Tony A,

You were right. It's nice to exchange with people like you. Thanks

Regards,
Michel

Michel,

A pleasure. It's great that the Forum helps join fellow coders the world over.

Francis,

Think of it as in Excel, in your primary example you only told WF to make a new row of data equal to the sum of the preceding two rows.

As Danny's method shows, giving WF further instructions to then modify the result in column 3 of the total row is the way forward.

As far as the recompute goes, you have to tell WF to recompute at the right time, even with subtotals. I always steer clear of using recompute in combination with FML if I can, prefering to control the contents of a cell by RECAP ing the indivdual cell contents as required.

T

p.s. check your email inbox - I think it's full

Francis,

Forgive me if I have no clue what the real need is but, glancing at the your code, it looks like it could be (almost) done alot easier with the use of RECOMPUTE.

DEFINE FILE CAR
RETAIL_SALES/D10 = SALES * RETAIL_COST;
COST/D10 = SALES * DEALER_COST;
SORTCONT/A1=DECODE
COUNTRY('W GERMANY' '1' 'ITALY' '1'
'JAPAN' '2' 'TAIWAN' '2' ELSE '3');
CONTINENT/A12=DECODE
COUNTRY('W GERMANY' 'EUROPE' 'ITALY' 'EUROPE'
'JAPAN' 'ASIA' 'TAIWAN' ASIA' ELSE 'AMERICA');
END
TABLE FILE CAR
SUM RETAIL_SALES COST
COMPUTE PROFITPCT/D6.1 = (RETAIL_SALES-COST) / RETAIL_SALES * 100;
BY SORTCONT NOPRINT
BY CONTINENT NOPRINT
ON CONTINENT RECOMPUTE AS 'TOTAL'
BY COUNTRY
WHERE CONTINENT NE 'AMERICA'
ON TABLE RECOMPUTE
END

If this is of no use to you just ignore me. I got into this thread late.

Mickey,

My example was a simplified fex using CAR to illustrate what I want to do in a more complicated program.

Cheers,

Francis,
designwallah.com is just wonderful!

Everyone should go visit it...

wonderful photos Francis

http://www.terlien.nl/frank

take a look here...

To me, RECAP in an FML report should behave the same way RECOMPUTE works in a non-FML report. The particular problem I had was an easy one to resolve, I have seen some very ugly stuff going on in an FML report, just to calculate percentages or subtotals... I wonder how easy it is to handle this in the FML GUI...

--------------------------------------------------
Re photos, thanks to both susannah and FrankDutch.

Cheers,

Francis, I disagree on this.
the advantage you have in FML is that you can do the same buth also a complete different calculation in the (sub)totals.
You can do things you normaly only could in excel, so if you would give that functionality up for just the recompute function, why should you then use the FML.

Frank