I would like to use a subtotal value in a define to claculate what percent of a hundred the cost is, or how much of the total cost does this expense represent of out 100


EX.

COSTS_____PERCENT
2_________5.4%
5_________13.5%
6_________16.2%
1_________2.7%
3_________8.1%
8_________21.6%
5_________13.5
7_________18.9
TOTAL
37________100

How can this be accomplished?

This message has been edited. Last edited by: Kerry,

ahhh yes, the basics. This is exactly what the PCT prefix does for you. Of course, there is always that pesky 'rounding' issue.

TABLE FILE CAR
SUM SEATS
   PCT.SEATS
BY CAR
ON TABLE COLUMN-TOTAL
END

will of course give you a total percentage of '95'. This is because the field SEATS is an integer. Do the following

DEFINE FILE CAR
	SEATS2/D6.2 = SEATS;
END

TABLE FILE CAR
SUM SEATS
    PCT.SEATS2
BY CAR
ON TABLE COLUMN-TOTAL
END

and the total for the percentages will equal 100 and all is well in the world.

Now what if i wanted it summed for every subtotoal

TITLE__COSTS_____PERCENT
A______2_________5.4%
_______5_________13.5%
_______6_________16.2%
_______1_________2.7%
_______3_________8.1%
_______8_________21.6%
_______5_________13.5
_______7_________18.9
TOTAL of A
_______37________100

TITLE__COSTS_____PERCENT
B______2_________5.4%
_______5_________13.5%
_______6_________16.2%
_______1_________2.7%
_______3_________8.1%
_______8_________21.6%
_______5_________13.5
_______7_________18.9
TOTAL of B
_______37________100

I see my subtle sarcasm did not work. Try the same code with a

BY COUNTRY

and add in

ON COUNTRY SUBTOTAL

All of this is covered in DETAIL in:

Creating Reports With WebFOCUS Language > Displaying Report Data > Manipulating Display Fields With Prefix Operators

section of the WebFOCUS Help menu item in the Dev Studio.

I would suggest a multiverb solution

DEFINE FILE CAR
TOTDC/D12.2=DEALER_COST;
SUBTOTDC/D12.2=DEALER_COST;
END
TABLE FILE CAR
SUM TOTDC NOPRINT
SUM 
    SUBTOTDC NOPRINT
BY COUNTRY 
SUM 
     DEALER_COST
     COMPUTE CPCT/D15.1 = DEALER_COST / SUBTOTDC*100; AS 'country,pct'
     COMPUTE TOTPCT/D15.1 = DEALER_COST / TOTDC*100; AS 'overall,pct'
BY COUNTRY
BY CAR
     
ON COUNTRY SUBTOTAL   AS 'subtotal'
HEADING
""
FOOTING
""
ON TABLE SET PAGE-NUM OFF 
ON TABLE COLUMN-TOTAL  
END

The only thing is that you also see a total of the percentage CPCT, that I would love to surpress.
Maybe somebody has a suggestion for this

ON TABLE RECOMPUTE

Im trying to do the first to suggestions by using:

SUM
SUBTOTDC NOPRINT
BY COUNTRY

before the SUM section in my code but i get this error:

(FOC020) THE SETS OF SORT PHRASES ARE INCONSISTENT
(FOC009) INCOMPLETE REQUEST STATEMENT
BYPASSING TO END OF COMMAND

Did not try RECOMPUTE I don't know what that is yet

Thanks for all the replys!!

This message has been edited. Last edited by: Charles Richards,

Charles

Maybe it is time for some basic training

Did you try my whole code from begin to end on your own computer?

Does it work?

Then try to understand what the code does

I created a multiverb report with some hidden fields (NOPRINT)
Remove the noprint code and see what happens

What I did was creating two extra fields that in fact give you the same result.
To calc the percentage I created two computed fields.

I hope this helps.

I have been messing around with your code and get it to work, tried using it with and without the NOPRINT...
However,

When i try to use the same concept on my current report it still gives me the error, so then I take it out and can get it to run showing all the data i want just doesnt have the last column with the percentages

As soon as I add another SUM it doesn't work

EX.

---------------
Running VERSION
----------------

DEFINE FILE DETAIL
CustNo/I6=IN_CUSTNO;
totSRP/D12.2=IN_PACK* IN_SRP;
UnitMove/I5=IN_QTY * IN_PACK;
totCost/D12.2=IN_UNITCOST * IN_PACK;
PercProf/D12.2=((IN_SRP - IN_UNITCOST) / IN_SRP)*100;
TotalProfit/D12.2=(totSRP - totCost);
subtotcost/D12.2=totCost;
END
TABLE FILE DETAIL

SUM
IN_ITEMCODE/I7 AS 'ItemCode'
IN_COMCLASS AS 'CC'
IN_DESC AS 'Item Description'
IN_PACK AS 'Pack'
IN_SIZE AS 'Size'
IN_QTY AS 'Cases Del.'
IN_SELL_UNITS AS 'Time Del.'
UnitMove AS 'Unit Move'
totCost AS 'Total Amount Paid'
totSRP AS 'Total Sell Value'
AVE.PercProf AS '% Profit'
TotalProfit
-----------------------------
would like to add percentages here for "totCost/ subtotoal.totCost" BY IN_COMCLASS
-----------------------------
BY DEPARTMENTCODE NOPRINT
BY IN_COMCLASS NOPRINT
BY IN_ITEMCODE NOPRINT
ON IN_COMCLASS SUBTOTAL
IN_QTY
totCost
totSRP AS '*TOTAL'
HEADING
""
FOOTING
""

========================================================

I may be getting the error becuase of the current way I have this Fex set up

Thanks for all the help, I am very new to WebFocus, thanks for the patience!

DEFINE FILE DETAIL
CustNo/I6=IN_CUSTNO;
totSRP/D12.2=IN_PACK* IN_SRP;
UnitMove/I5=IN_QTY * IN_PACK;
totCost/D12.2=IN_UNITCOST * IN_PACK;
PercProf/D12.2=((IN_SRP - IN_UNITCOST) / IN_SRP)*100;
TotalProfit/D12.2=(totSRP - totCost);
subtotcost/D12.2=totCost;
END
TABLE FILE DETAIL
SUM SUBTOTCOST NOPRINT
BY DEPARTMENTCODE NOPRINT
BY IN_COMCLASS NOPRINT
SUM
IN_ITEMCODE/I7 AS 'ItemCode'
IN_COMCLASS AS 'CC'
IN_DESC AS 'Item Description'
IN_PACK AS 'Pack'
IN_SIZE AS 'Size'
IN_QTY AS 'Cases Del.'
IN_SELL_UNITS AS 'Time Del.'
UnitMove AS 'Unit Move'
totCost AS 'Total Amount Paid'
totSRP AS 'Total Sell Value'
AVE.PercProf AS '% Profit'
TotalProfit
-*----------------------------
COMPUTE PCTTOT/D9.2%=totCost/SUBTOTCOST;

-*-----------------------------
BY DEPARTMENTCODE NOPRINT
BY IN_COMCLASS NOPRINT
BY IN_ITEMCODE NOPRINT
ON IN_COMCLASS SUBTOTAL
IN_QTY
totCost
totSRP AS '*TOTAL'
HEADING
""
FOOTING
""
END

this should work well

Thanks So much I was able to get it working!

Now is there anyway to arrange it

BY PCTTOT NOPRINT

after

BY IN_COMCLASS NOPRINT ?

if you do a BY HIGHEST TOTCOST NOPRINT whitin the comclass it will work that way