November 01, 2004, 03:19 PM
susannahFORMAT EXL2K FORMULA totalling problem
ON TABLE COLUMN-TOTAL or SUBTOTAL or any variant produces the wrong results in excel formula output. The results are the same as ON TABLE SUMMARIZE, so a column that is produced by a formula cannot be subtotaled or totalled, it can only be recomputed...which causes real problems when the computation in aggregate produces a wrong answer (price * quantity, for ex).
Anybody got a work around?
November 02, 2004, 07:59 AM
PiipsterWill an ON TABLE RECAP work?
Use an ON TABLE SUBFOOT to position things afterward.
August 28, 2007, 03:35 PM
SauPaHi,
I am facing a similar problem.
Actually, the 2 version, v532 and v716 work differently. ( we recently upgraded to v716)
Here is a sample fex.
--------------------------------------------
TABLE FILE CAR
SUM
DEALER_COST
RETAIL_COST
COMPUTE DFN_PCT/F12.2%= IF DEALER_COST EQ 0 THEN 0 ELSE RETAIL_COST / DEALER_COST; AS 'AFLD'
BY COUNTRY SUBTOTAL
BY CAR
BY MODEL
ON TABLE RECOMPUTE AS "TOTAL"
ON TABLE PCHOLD FORMAT EXL2K FORMULA
END
--------------------------------------------
v532 Report Looks Like COUNTRY CAR MODEL DEALER_COST RETAIL_COST AFLD
ENGLAND JAGUAR V12XKE AUTO 7,427 8,878 119.54%
XJ12L AUTO 11,194 13,491 120.52%
JENSEN INTERCEPTOR III 14,940 17,850 119.48%
TRIUMPH TR7 4,292 5,100 118.83%
*TOTAL COUNTRY ENGLAND 37,853 45,319 119.72%
----------------------------------
v716 Report Looks Like COUNTRY CAR MODEL DEALER_COST RETAIL_COST AFLD
ENGLAND JAGUAR V12XKE AUTO 7,427 8,878 1.20%
ENGLAND JAGUAR XJ12L AUTO 11,194 13,491 1.21%
ENGLAND JENSEN INTERCEPTOR III 14,940 17,850 1.19%
ENGLAND TRIUMPH TR7 4,292 5,100 1.19%
*TOTAL COUNTRY ENGLAND 37,853 45,319 4.78%
-----------------------------
Any idea how to make them consistent ?
August 29, 2007, 09:12 AM
jgelonaChange your COMPUTE to:
COMPUTE DFN_PCT/F12.2%= IF DEALER_COST EQ 0 THEN 0 ELSE 100*(RETAIL_COST / DEALER_COST); AS 'AFLD'
That will get your detail correct.
But as to why the RECOMPUTE is not working, looks like a bug to me. Also, if you look at the formula in the spreadsheet for "*TOTAL COUNTRY ENGLAND", it is doing a SUM and not a RECOMPUTE.
August 29, 2007, 10:13 AM
Francis MarianiIt's a bug and I abandoned using EXL2K Formula.
Cheers if it works in releases after v5.3.2.
And where have you been lately?
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server