I'm using FML RECAP to sum up multipe rows. One column is a percent change of two other columns. The COMPUTE I am using works correctly and shows the right answer for each of the individual rows, but not does not provide an answer for the total row? The sum of the columns is there, but not the percent change result (it shows '.'). I looked and did not see anything like a 'RECOMPUTE' for FML. I'm thinking that is is attempting to add up the percentages from the individual rows, which is not the correct answer. How do I tell FML that for this row and column, re-calculate the COMPUTE and not use the RECAP summation? Thanks.

Matthew,

You know what I'm going to say .... draw up an example of what you are trying to do using GGSALES or similar. Seeing the code that you are using often assists in identifying the solution quicker

Matthew,

Is this something similar to what you're looking for ?

TABLE FILE GGSALES
SUM UNITS AS 'ACTUAL'
BUDUNITS AS 'BUDGET'
COMPUTE PCT_DIFF/F9.4% = (UNITS-BUDUNITS) / BUDUNITS * 100 ; AS 'ACTUAL VS BUDGET'

FOR CATEGORY
'Coffee' LABEL R1 AS 'COFFEE' OVER
'Gifts' LABEL R2 AS 'GIFTS' OVER
'Food' LABEL R3 AS 'FOOD' OVER

RECAP TOT(1,2,1) = R1 + R2 + R3 ; AS 'TOTAL' OVER
RECAP TOT(3) = ( TOT(1) - TOT(2) ) / TOT(2) * 100 ; AS 'TOTAL'

END

You have a column that has a percentage. In the RECAPs, I've used 2. The first one says

RECAP TOT(1,2,1) - recap from column 1 to column 2 in increments of 1 (hence the (1,2,1). Then do a recap for column 3 (which is the percentage column). This recap recreates the compute in the SUM from above.

Try it and verify.

Ken

Here is what I tried based in your suggestion....<br />

TABLE FILE REV_HOLD<br />SUM
CP_ACT/D8CB AS 'Current Actuals'; 
PP_ACT/D8CB AS 'Prior Actuals'; 
COMPUTE PP_ACT_V/D8CB = 
CP_ACT - PP_ACT; AS '
vs Prior actuals B/(W)'; 
COMPUTE PP_ACT_VP/D6.1%B  
= IF PP_ACT EQ 0 THEN '.0%' 
ELSE  PP_ACT_V / PP_ACT * 
100; AS 'Current actuals vs Prior 
actuals B/(W) %'; YP_ACT/D8CB AS 
'Prior year'; 
COMPUTE YP_ACT_V/D8CB = 
CP_ACT - YP_ACT; 
AS 'Current actuals vs Prior 
year B/(W)'; 
COMPUTE YP_ACT_VP/D6.1%B  = 
IF YP_ACT EQ 0 THEN '.0%' ELSE  
YP_ACT_V / YP_ACT * 100; AS 
'Current actuals vs Prior year B/(W) %';
CP_EST/D8CB AS 'Forecast'; 
COMPUTE CP_EST_V/D8CB =
 CP_ACT - CP_EST; AS 'Current actuals vs Forecast B/(W)'; 
COMPUTE CP_EST_VP/D6.1%B  = IF CP_EST EQ 0 THEN '.0%' ELSE  CP_EST_V / CP_EST * 
100; AS 'Current actuals vs 
Forecast B/(W) %';
CP_BUD/D8CB AS 'Budget &CP'; 
COMPUTE CP_BUD_V/D8CB = 
CP_ACT - CP_BUD; AS 'Current 
actuals vs Budget B/(W)'; 
COMPUTE CP_BUD_VP/D6.1%B  = 
IF CP_BUD EQ 0 THEN '.0%' ELSE  CP_BUD_V / CP_BUD * 100; AS 
'Current actuals vs Budget B/(W) %';
FOR PROD_TIER_NM
"ALL PRODUCTS" LABEL R001 OVER
"GROUP A" LABEL R002 OVER
'PRODUCT 1' AS 'Product One' 
LABEL R003 OVER
'PRODUCT 2' AS 'Product Two' 
LABEL R004 OVER
RECAP R005(2,4,1)=R003+R004; AS 
'Total Group A' OVER
RECAP R005(6,7,1)=R003+R004; AS 
'Total Group A' OVER
RECAP R005(9,10,1)=R003+R004; AS 
'Total Group A' OVER
RECAP R005(12,13,1)=R003+R004; AS 
'Total Group A' OVER
RECAP R005(5,14,3)=R005(5)=(R005(2)-R005(3))
/R005(3); AS 'Total Group A' OVER
"GROUP B" LABEL R006 OVER
'PRODUCT 3' AS 'Product Three'  
LABEL R007 OVER
'PRODUCT 4' AS 'Product Four' 
LABEL R008 OVER
RECAP R009(2,4,1)=R007+R008; AS 
'Total Group B' OVER
RECAP R009(6,7,1)=R007+R008; 
AS 'Total Group B' OVER
RECAP R009(9,10,1)
=R007+R008; AS 'Total Group B' OVER
RECAP R009(12,13,1)=R007+R008; 
AS 'Total Group B' OVER
RECAP R009(5,14,3)=(R009(2)-R009(3))
/R009(3); AS 'Total Group B' OVER

This is not working .... any help is appreciated.

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

At a quick glance, shouldn't CP_ACT be column 1 instead of 2.

If I get the chance later on, I'll have another look. I'm heading out on vacation. Hopefully I can provide some info to you before then.

I thought the lable (e.g. 'Product One')
was Column 1? Anyway.
I change the code to try that and
It still did not work.
I simplifued the code as follows:

RECAP R005=R003+R004; 
AS 'Group A' 
OVER RECAP R005(2,14,3)
/A9=IF R005(*-2) EQ 0 THEN '.0%' 
ELSE IF ABS(R005(*-1)/R005(*-2)*100) 
LT 1000 THEN FTOA(R005(*-1)/R005(*-2)*100,'
(D6.1%B)',R005) ELSE 'NM'; AS 
'Group A' OVER

I'm hoping that this will
put the sum in all columns on the
first RECAP statement then go back
and put in the percent change in
columns 5,8,11&14 on the second
RECAP statement. But it still is
not showing any results in columns
5,8,11 & 14?

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

Matthew,

I think one of your problems is that you're combining text into a numeric cell (in your first code "snippet").

quote:

COMPUTE PP_ACT_VP/D6.1%B = IF PP_ACT EQ 0 THEN '.0%' .........

Whilst you can achieve this to control the display of the output, it often mucks up the ability to perform RECAPs etc. It is also not really valid syntax(?).

My advice is not to change cell content attributes (i.e. numeric or alphanumeric), which I see you have attempted in your latest code snippet.

It looks to me as though your main requirement in your attempts, is to control the display and suppress leading zeroes. I think you'll find that this is default behaviour, so you only need to code - IF PP_ACT EQ 0 THEN 0 ELSE ......

I also think that you need to modify your code slightly to achieve your results. Remember that the RECAP will overwrite any cell contents so you can just add rows 3 and 4 and place the results in row 5, then you can add your RECAP to overwrite the cell contents in rows 5 through end in steps of 3 using the syntax that Ken gave you but instead of specifying an end column just put an asterisk (*) to specify the last column requirement -

RECAP R005=R003+R004; AS 'Total Group A' OVER
RECAP R005(5,*,3)=(R005(2)-R005(3))/R005(3); AS 'Total Group A' OVER

This should give you what you require.

A tip - don't over code, let WebFOCUS take the brunt - most of the time

Regards

Tony

Matthew,

I can see what the problem is (I think).

The first problem is that the last columns do not get included in the RECAP - the use of the asterisk above will solve that - but see below.

The columns that you are trying to RECAP from are intended to be offsets(?) from the current column. At the moment you are using fixed column references - R005(3) will always use the contents from col 3 in row labelled R005. To use col 3 from col 5 and col 6 from col 8 in the row labelled R005 you would require the syntax R005(*-2).

Your main problem though is that, for some reason, there is a hidden column (or there is in my example [rel 5.3.2 on XP] using a cut from your code against GGSALES ), so the column you are seeing as column 5 is actually column 6 and the increments will then be 4. Therefore I think you need the code -

RECAP R005=R003+R004; AS 'Total Group A' OVER
RECAP R005(6,*,4)=(R005(*-1)-R005(2))/R005(*-1)*100; OVER
etc. ....

Hope this helps and good luck.

Tony

Edited to add -

Just remembered the reason for the extra (hidden) column and that is that you have a compute with an IF statement (I think)

Thanks for the help. I did discover the hidden columns but doing some debugging/testing. But I did not know why they were there. The If in the COMPUTE seems like a logical explanation. However, I did adjust the colums start and increments and still cannot get it to work. I even hard coded the column reference with no luck. I will keep trying.

Matthew,
Cut and paste this code
which was dervied from yours
buut modified to use the
GGSALES table. As far as
I can tell it is doing what
you require and therefore
might help you understand
the references.

TABLE FILE GGSALES
SUM UNITS/D8CB 
AS 'Current Actuals';
DOLLARS/D8CB 
AS 'Prior Actuals';
COMPUTE PP_ACT_V/D8CB = 
DOLLARS - UNITS; AS 
'Current actuals 
vs Prior actuals B/(W)';
COMPUTE PP_ACT_VP/D6.1%B  = 
IF DOLLARS EQ 0 THEN 0 ELSE 
PP_ACT_V / DOLLARS * 100; 
AS 'Current actuals vs 
Prior actuals B/(W) %'
DOLLARS/D8CB AS 
Prior year';
COMPUTE YP_ACT_V/D8CB = 
DOLLARS - UNITS;
AS 'Current actuals vs 
Prior year B/(W)';
 COMPUTE YP_ACT_VP/D6.1%B  = 
IF DOLLARS EQ 0 THEN 0 
ELSE YP_ACT_V / DOLLARS * 100;   
AS 'Current actuals vs 
Prior year B/(W) %'; DOLLARS/D8CB AS 'Forecast';
COMPUTE CP_EST_V/D8CB = 
DOLLARS - UNITS;
AS 'Current actuals vs 
Forecast B/(W)';COMPUTE 
CP_EST_VP/D6.1%B  = 
IF DOLLARS EQ 0 THEN 0
 ELSE CP_EST_V / DOLLARS * 100;                        AS 'Current actuals 
vs Forecast B/(W) 
 BUDDOLLARS/D8CB AS 'Budget ';
COMPUTE CP_BUD_V/D8CB = 
BUDDOLLARS - UNITS;
AS 'Current actuals vs 
Budget B/(W)';
COMPUTE CP_BUD_VP/D6.1%B  = 
IF BUDDOLLARS EQ 0 THEN 0                           ELSE CP_BUD_V / 
BUDDOLLARS * 100;
 AS 'Current actuals vs Budget B/(W) %';
FOR PRODUCT
"ALL PRODUCTS" LABEL R001 
OVER "GROUP A" LABEL R002 OVER
'Capuccino' AS 'Product One' 
LABEL R003 OVER
'Espresso' AS 'Product Two' LABEL 
R004 OVER
RECAP R005=R003+R004; 
AS 'Total Group A' OVER
RECAP R005(6,*,4)=(R005(*-1)-
R005(2))/R005(*-1)*100; OVER
"" OVER "GROUP B" LABEL R006 OVER
'Biscotti' AS 'Product Three' 
LABEL R007 OVER
'Croissant' AS 'Product Four' 
LABEL R008 OVER
RECAP R009=R007+R008; AS 'Total 
Group B' OVER
RECAP 009(6,*,4)=(R009(*-1)-R009(2))
/R009(*-1)*100;
ON TABLE SET HTMLCSS ON
ON TABLE SET PAGE-NUM OFF
ON TABLE PCHOLD AS MYFML 
FORMAT HTML
ON TABLE SET STYLE *
TYPE=RECAP, BACKCOLOR=SILVER, $
ENDSTYLE
END

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

Matthew,

There is a known problem using relative addressing with RECAP in 5.3.3 - check it out

Thanks for all your help Tony. I do have it working now. Although not exactly as I really want it. We use a convention that if the % change is GT 999.9 to put "NM" for 'Not Meaningful'. I was trying to do this by converting the numbers to an alpha field with FTOA(), but that seamed to cause the RECAP not to work. Once I changed the 'NM' to just 999.9 to keep everything a numeric field it all worked.