I'm working on a pretty complicated report, and I'm having an issue with the display of percentages in the final output.

The column is a "Percent to Total" for the figure, and I've used dialog manager commands to read the total figures off a subfile, then I defined the field as (figure/total)*100.

With 7/10 of the fields that I did like this, my ON TABLE RECOMPUTE command works (shows 100% on the grand total line), but for the other 3, it shows 10% instead.

I have no idea why it would do this, do any of you? The sum of the rows is 100%, it just doesn't seem to be adding correctly. The rows themselves are calculating correctly, too, which is throwing me off.

The only difference between these 3 fields and the others is that the total is defined before the file (any other way seemed to give me issues), while the other totals are pulled from the subfile as mentioned.

Example code:

DEFINE FILE LMPRIMEG
	ANN_TTL_SLS_QTY/P10		= &TTL_SLS_QTY / (&A_SLS_U/100);
	ANN_TTL_NET_SLS/P10		= &TTL_NET_SLS / (&A_SLS_D/100);
	ANN_TTL_POS_MGN/P10		= &TTL_POS_MGN / (&A_MARGIN/100);
END
TABLE FILE LMPRIMEG
SUM
	COMPUTE ANN_PTT_NET_SLS/P5.2%	 = (ANN_SALES/ANN_TTL_NET_SLS)*100;							AS 'Annualized % to Total Sales $'
	COMPUTE ANN_PTT_SLS_QTY/P5.2%	 = (ANN_SALES_QTY/ANN_TTL_SLS_QTY)*100;						AS 'Annualized % to Total Sales Units'
	COMPUTE ANN_PTT_POS_MGN/P5.2%	 = (ANN_POS_MARGIN/ANN_TTL_POS_MGN)*100;					AS 'Annualized % to Total GM $'
	COMPUTE ANN_PTT_OH_DOLL/P5.2%	 = (STOCK_COST/&TTL_OH_DOLL)*100;						AS 'Annualized % to Total On Hand $'
	COMPUTE ANN_PTT_ONH_INV/P5.2%	 = (STOCK_QTY/&TTL_ONH_INV)*100;						AS 'Annualized % to Total On Hand Units'
END

Please let me know what you think or if you need more info!

This message has been edited. Last edited by: FP Mod Chuck,

Not having any BY field ? So, it means that you only create a total reading the whole file ?
Which of the fields you have an issue ?

You need to pay attention using DEFINEs and COMPUTEs. Their result is not the same.

Using the DEFINEs what FOCUS does it's performing the calculation at each single rows where it then perform a total.
Using the COMPUTEs what FOCUS does it's performing the calculation once any DEFINEs are completed and WHERE clauses applied.

Meaning that for each row read from LMPRIMEG it a value for : ANN_TTL_SLS_QTY/P10 = &TTL_SLS_QTY / (&A_SLS_U/100); then it sum each value together to then perform : COMPUTE ANN_PTT_SLS_QTY/P5.2% = (ANN_SALES_QTY/ANN_TTL_SLS_QTY)*100;

So let say that you have 5 rows as per below in your file and that &TTL_SLS_QTY and &A_SLS_U have respectively the value of 500 and 1000

ROWID ANN_SALES_QTY
1     600
2     200
3     800
4     100
5     700

FOCUS will perform 5 times your DEFINE : ANN_TTL_SLS_QTY/P10 = &TTL_SLS_QTY / (&A_SLS_U/100)
Then the COMPUTE is performed where
- the previous DEFINE is summed (5 x 50) which become the value of ANN_TTL_SLS_QTY used in the COMPUTE
- the value of ANN_SALES_QTY is summed : 600 + 200 + 800 + 100 + 700 = 2400
- finally (ANN_SALES_QTY/ANN_TTL_SLS_QTY)*100 is performed : (2400 / 250) * 100 = 960

See below sample

-SET &VAL1 = 10;
-SET &VAL2 = 15;
DEFINE FILE CAR
TOT_DEF /P10 = &VAL1 / (&VAL2 / 100);
END

TABLE FILE CAR
PRINT SEATS
      TOT_DEF
WHERE READLIMIT EQ 5;
WHERE RECORDLIMIT EQ 5;
HEADING
"Values using PRINT"
END
-RUN

TABLE FILE CAR
SUM SEATS
    TOT_DEF
WHERE READLIMIT EQ 5;
WHERE RECORDLIMIT EQ 5;
HEADING
"Values using SUM"
END
-RUN

TABLE FILE CAR
SUM COMPUTE TEST/P10 = (SEATS / TOT_DEF) * 100;
WHERE READLIMIT EQ 5;
WHERE RECORDLIMIT EQ 5;
HEADING
"Result of COMPUTE"
END
-RUN

quote:

Not having any BY field ? So, it means that you only create a total reading the whole file ?

Sorry, there IS a BY field in the final file. Let me kind of draw out what's happening, if I can:

Layout    Annualized Sales    % To Total Sales $
A              $450                45%
B              $300                30%
C              $100                10%
D              $100                10%
E              $50                 5%
-----------------------------------------------
TOTAL          $1000               10%

So clearly, the rows should add up to 100%, but for some reason, they are adding to "10%"

More specifics from my original post: &TTL_NET_SLS (and the other similar variables) is pulled from the same subfile (LPRIMEG), but with NO BY FIELD, meaning that it's a summed total of all sales. &A_SLS_D is a percent between 1 - 100 entered by the user on the HTML selection screen. I've found the most accuracy if I wait to divide by 100 till here at the end.

My lead was out over the weekend, and I just asked him about this, and he gave the advice to "just take it out of the grand total line". I may end up doing this, but I would really like to just have those nice, neat 100%s in the grand total line.

Since your

% To Total Sales $

column seems to be a computed value, it will also be computed (recalculated) at the grand total and not the sum as you want depending on the syntax you've used to perform the total line.

See below sample on Grand Total usage

-SET &VAL1 = 10;
-SET &VAL2 = 15;
DEFINE FILE CAR
TOT_DEF /P10 = &VAL1 / (&VAL2 / 100);
END

TABLE FILE CAR
SUM SEATS
    TOT_DEF
    COMPUTE TEST/P10 = (SEATS / TOT_DEF) * 100;
BY COUNTRY
HEADING
"Grand Total Sum Computed Field"
ON TABLE COLUMN-TOTAL
END
-RUN

TABLE FILE CAR
SUM SEATS
    TOT_DEF
    COMPUTE TEST/P10 = (SEATS / TOT_DEF) * 100;
BY COUNTRY
HEADING
"Grand Total Recompute Computed Field"
ON TABLE SUMMARIZE
END
-RUN

My grand total line is an

 ON TABLE RECOMPUTE 

, with several fields from the body of the report excluded. (Adding these % to total ones to the excluded list is my coworker's suggestion)

Since the calculation is the sales/total, I assumed at the total line that it would take the summed total from the sales column and use that in the calculation (i.e.

sum(sales)/ttl_sales

)

So even if it's doing a recompute, I figured that would be fine, as the calculation should still work.

The trouble with using Summarize is that I need a recompute on several of the fields (averages, other computed fields) that won't allow for the line to be changed from recompute to summarize.

How does "sales" is calculated ?
How does "ttl_sales" is calculated ?
where those fields come from ? They were not in other of your post...

Can you please share your whole code, it will be easier to figure where is your problem ?

It's too much assumption up to now.
Partial code won't help us helping you

Tks

I was simplifying. My apologies.

By "sales" I was referring to ANN_SALES, and by "ttl_sales" I was referring to ANN_TTL_NET_SLS.

It seems like I should just not include the 3 incorrect % to total fields in the Grand Total line, but I'll go ahead and post the code.

DEFINE FILE LMPRIMEG
	TTL_LW_INV_COST/P19M 	= LW_INVENTORY_COST + INV_COST;
	TTL_LW_INV_UNIT/P19		= LW_INVENTORY_UNIT + INV_QTY;

	ANN_TTL_SLS_QTY/P10		= &TTL_SLS_QTY / (&A_SLS_U/100);
	ANN_TTL_NET_SLS/P10		= &TTL_NET_SLS / (&A_SLS_D/100);
	ANN_TTL_POS_MGN/P10		= &TTL_POS_MGN / (&A_MARGIN/100);
END

TABLE FILE LMPRIMEG
SUM
	ART_CNT 	AS 'Article Count Assigned'
	COMPUTE ANN_SITE/I10 = SITE_ACT_CNT;  AS 'Site Count Active'
	COMPUTE A_AVGSISALES/P19CM = CM_G_TY_WTD_NET_SALES/(&A_SLS_D/100)) / SITE_ACT_CNT;	AS 'Annualized Avg Site Net Sales'
	COMPUTE A_AVGSISALESQTY/P19C = CM_G_TY_WTD_NET_SALES_QTY / (&A_SLS_U/100)) / SITE_ACT_CNT;	AS 'Annualized Avg Site Net Sales Qty'
	COMPUTE ANN_SALES/P19CM = CM_G_TY_WTD_NET_SALES / (&A_SLS_D/100);	AS 'Annualized Net Sales'
	COMPUTE ANN_SALES_QTY/P19C = CM_G_TY_WTD_NET_SALES_QTY / (&A_SLS_U/100); AS 'Annualized Net Sales Qty'
	COMPUTE ANN_COGS/P19CM = ANN_SALES - 
 CM_G_TY_WTD_NET_POS_MARGIN / (&A_MARGIN/100));	AS 'Annualized Net COGS'
	COMPUTE ANN_POS_MARGIN/P19CM = CM_G_TY_WTD_NET_POS_MARGIN / (&A_MARGIN/100); AS 'Annualized Net POS Margin'
	COMPUTE ANN_POS_MARGIN_PCT/P19.2%C = (ANN_POS_MARGIN / ANN_SALES)*100; AS 'Annualized Net POS Margin %'
	COMPUTE ANN_TY_GL_STOCK_COST/P19CM = TY_GL_STOCK_COST; AS 'Annualized Store OH Stock Cost'
	COMPUTE ANN_TY_GL_STOCK_QTY/P19C = TY_GL_STOCK_QTY; AS 'Annualized Store OH Stock Qty'
	COMPUTE AVG_INV_COST/P19CM	 	 = TTL_LW_INV_COST / (&NUMWEEKS + 1);	AS 'Annualized Avg Inv Cost'
	COMPUTE AVG_INV_QTY/P19C 		 = TTL_LW_INV_UNIT / (&NUMWEEKS + 1);	AS 'Annualized Avg Inv Units'
	COMPUTE ANN_WEEK_COUNT/I2 		 = 52;				AS 'Annualized Weeks with Sales'
	COMPUTE ANN_OHDOLPSITE/P19CM		 = AVG_INV_COST / SITE_ACT_CNT; 		AS 'Annualized Avg On Hand Dollars per Site'
	COMPUTE ANN_OHUNITPSITE/P19C		 = AVG_INV_QTY / SITE_ACT_CNT; 			AS 'Annualized Avg On Hand Units per Site'
	COMPUTE ANN_PTT_NET_SLS/P5.2%	 = (ANN_SALES/ANN_TTL_NET_SLS)*100;		AS 'Annualized % to Total Sales $'
	COMPUTE ANN_PTT_SLS_QTY/P5.2%	 = (ANN_SALES_QTY/ANN_TTL_SLS_QTY)*100;	AS 'Annualized % to Total Sales Units'
	COMPUTE ANN_PTT_POS_MGN/P5.2%	 = (ANN_POS_MARGIN/ANN_TTL_POS_MGN)*100;					AS 'Annualized % to Total GM $'
	COMPUTE ANN_PTT_OH_DOLL/P5.2%	 = (TY_GL_STOCK_COST/&TTL_OH_DOLL)*100;	AS 'Annualized % to Total On Hand $'
	COMPUTE ANN_PTT_ONH_INV/P5.2%	 = (TY_GL_STOCK_QTY/&TTL_ONH_INV)*100;	AS 'Annualized % to Total On Hand Units'

	COMPUTE ANNUAL/A10			 = '**********';		AS '**********'

	ART_CNT 	AS 'Article Count Assigned'
	SITE_ACT_CNT  					AS 'Site Count Active'
	COMPUTE AVGSISALES/P19CM = CM_G_TY_WTD_NET_SALES/SITE_ACT_CNT; 			AS 'Avg Site Net Sales'
	COMPUTE AVGSISALESQTY/P19C = CM_G_TY_WTD_NET_SALES_QTY/SITE_ACT_CNT; 	AS 'Avg Site Net Sales Qty'
	CM_G_TY_WTD_NET_SALES/P19CM
	CM_G_TY_WTD_NET_SALES_QTY/P19C
	CM_G_TY_WTD_NET_COGS/P19CM
	CM_G_TY_WTD_NET_POS_MARGIN/P19CM
	COMPUTE NET_POS_MARGIN_PCT/P19.2% = (CM_G_TY_WTD_NET_POS_MARGIN/CM_G_TY_WTD_NET_SALES)*100; AS 'Net POS Margin %'
	TY_GL_STOCK_COST/P19CM 					AS 'Store OH Stock Cost'
	TY_GL_STOCK_QTY/P19C	AS 'Store OH Stock Qty'
	AVG_INV_COST/P19CM				AS 'Avg Inv Cost'
	AVG_INV_QTY/P19C					AS 'Avg Inv Unit'
	WEEK_COUNT/I5 					AS 'Weeks with Sales'
	COMPUTE OHDOLPSITE/P19CM	 	 = AVG_INV_COST / SITE_ACT_CNT; 			AS 'Avg On Hand Dollars per Site
	COMPUTE OHUNITPSITE/P19C		 = AVG_INV_QTY / SITE_ACT_CNT; 				AS 'Avg On Hand Units per Site''
	COMPUTE PTT_NET_SLS/P5.2%		 = (CM_G_TY_WTD_NET_SALES/&TTL_NET_SLS)*100;					AS '% to Total Sales $'
	COMPUTE PTT_SLS_QTY/P5.2%		 = (CM_G_TY_WTD_NET_SALES_QTY/&TTL_SLS_QTY)*100;				AS '% to Total Sales Units'
	COMPUTE PTT_POS_MGN/P5.2%		 = (CM_G_TY_WTD_NET_POS_MARGIN/&TTL_POS_MGN)*100;				AS '% to Total GM $'
	COMPUTE PTT_OH_DOLL/P5.2%		 = (TY_GL_STOCK_COST/&TTL_OH_DOLL)*100;				AS '% to Total On Hand $'
	COMPUTE PTT_ONH_INV/P5.2%		 = (TY_GL_STOCK_QTY/&TTL_ONH_INV)*100;				AS '% to Total On Hand Units'

	BY LAYMOD
	BY LM_DESCR

ON TABLE RECOMPUTE
ANN_SITE A_AVGSISALES A_AVGSISALESQTY ANN_SALES ANN_SALES_QTY ANN_COGS
ANN_POS_MARGIN ANN_POS_MARGIN_PCT ANN_TY_GL_STOCK_COST ANN_TY_GL_STOCK_QTY
ANN_OHDOLPSITE ANN_OHUNITPSITE ANN_PTT_NET_SLS ANN_PTT_SLS_QTY ANN_PTT_POS_MGN
ANN_PTT_OH_DOLL ANN_PTT_ONH_INV ANNUAL

SITE_ACT_CNT AVGSISALES AVGSISALESQTY CM_G_TY_WTD_NET_SALES
CM_G_TY_WTD_NET_SALES_QTY CM_G_TY_WTD_NET_COGS CM_G_TY_WTD_NET_POS_MARGIN
NET_POS_MARGIN_PCT TY_GL_STOCK_COST TY_GL_STOCK_QTY OHDOLPSITE OHUNITPSITE
PTT_NET_SLS PTT_SLS_QTY PTT_POS_MGN PTT_OH_DOLL PTT_ONH_INV

ON TABLE PCHOLD AS LM_PRODUCTIVITY FORMAT &OFORMAT
END

Again, &TTL_NET_SLS and the other 2 TTL amper variables are from a subfile and read out before the above code in the fex. That code is here:

TABLE FILE LMPRIMEA
SUM
	CM_G_TY_WTD_NET_SALES_QTY AS 'TTL_SLS_QTY'
	CM_G_TY_WTD_NET_SALES AS 'TTL_NET_SLS'
	CM_G_TY_WTD_NET_POS_MARGIN AS 'TTL_POS_MGN'

ON TABLE HOLD AS PCTTOTTL
END
-RUN

-READFILE PCTTOTTL
-SET &TTL_SLS_QTY = TRUNCATE(&TTL_SLS_QTY);
-SET &TTL_SLS_QTY = IF &TTL_SLS_QTY = '' THEN 0 ELSE &TTL_SLS_QTY;

-*-READFILE PCTTOTTL
-SET &TTL_NET_SLS = TRUNCATE(&TTL_NET_SLS);
-SET &TTL_NET_SLS = IF &TTL_NET_SLS = '' THEN 0 ELSE &TTL_NET_SLS;

-*-READFILE PCTTOTTL
-SET &TTL_POS_MGN = TRUNCATE(&TTL_POS_MGN);
-SET &TTL_POS_MGN = IF &TTL_POS_MGN = '' THEN 0 ELSE &TTL_POS_MGN;

-*-TYPE Total Net Sales Quantity is &TTL_SLS_QTY
-*-TYPE Total Net Sales is &TTL_NET_SLS
-*-TYPE Total POS Margin is &TTL_POS_MGN


TABLE FILE LMPRIMEG
SUM
	TY_GL_STOCK_COST AS 'TTL_OH_DOLL'
	TY_GL_STOCK_QTY  AS 'TTL_ONH_INV'

ON TABLE HOLD AS PCTINV
END
-RUN

-READFILE PCTINV
-SET &TTL_ONH_INV = TRUNCATE(&TTL_ONH_INV);
-SET &TTL_ONH_INV = IF &TTL_ONH_INV = '' THEN 0 ELSE &TTL_ONH_INV;
-*-TYPE Total On Hand Inventory is &TTL_ONH_INV

-READFILE PCTINV
-SET &TTL_OH_DOLL = TRUNCATE(&TTL_OH_DOLL);
-SET &TTL_OH_DOLL = IF &TTL_OH_DOLL = '' THEN 0 ELSE &TTL_OH_DOLL;
-*-TYPE Total On Hand Dollars is &TTL_OH_DOLL

The &A_* amper variables are whole numbers 1-100 entered by the user.

Please let me know if anything else is unclear, and I will be glad to give more info.

From what I can see from your code, here are my comment :

Why do you have several times the same field COMPUTEd or displayed ? I don't know your requirement, but it seems weird for me this layout.
Since you don't have any special stuff regarding those fields, they should display the same result. Below some fields from many.

Included twice

ART_CNT 	AS 'Article Count Assigned'

Same thing

COMPUTE ANN_TY_GL_STOCK_QTY/P19C = TY_GL_STOCK_QTY; AS 'Annualized Store OH Stock Qty'
…
TY_GL_STOCK_QTY/P19C	AS 'Store OH Stock Qty'

I agree that for some the result may differ such as the one below, but for those above sample, they will display the same result twice. Only the column's title change.

COMPUTE A_AVGSISALES/P19CM = CM_G_TY_WTD_NET_SALES/(&A_SLS_D/100)) / SITE_ACT_CNT;	AS 'Annualized Avg Site Net Sales'
…
COMPUTE AVGSISALES/P19CM = CM_G_TY_WTD_NET_SALES/SITE_ACT_CNT; 			AS 'Avg Site Net Sales'

Also, pay attention to match properly the quotes (open/close) in your AS titles

AS 'Avg On Hand Dollars per Site
AS 'Avg On Hand Units per Site''

As for your "problem" to display 100% instead of the RECOMPUTEd value, the simplest option is not to display these field total as suggested by your co-worker.
Other option is possible, but will need much more code and other technics.

It is in my requirements to display all of those fields twice. They want them reported the same way for both the "annual" and the "actual" reports, but with different names. Normally, I wouldn't even bother computing them again, but because I am doing the RECOMPUTE with fields missing, I needed different names for these fields that are the same thing (WebFOCUS doesn't much like when the same field is in a statement like that more than once).

Thanks for your help! I'll just exclude all of the percentages.

Please update your first post then add [SOLVED] at the beginning of the subject