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
How can this be accomplished?This message has been edited. Last edited by: Kerry,
WebFOCUS 7.6 Windows, All Outputs
June 06, 2011, 11:32 AM
rfbowley
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.
Robert F. Bowley Jr. Owner TaRa Solutions, LLC
In WebFOCUS since 2001
June 06, 2011, 01:18 PM
Charles Richards
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
WebFOCUS 7.6 Windows, All Outputs
June 06, 2011, 01:45 PM
rfbowley
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.
Robert F. Bowley Jr. Owner TaRa Solutions, LLC
In WebFOCUS since 2001
June 07, 2011, 06:56 AM
FrankDutch
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
Frank
prod: WF 7.6.10 platform Windows, databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7 test: WF 7.6.10 on the same platform and databases,IE7
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,
WebFOCUS 7.6 Windows, All Outputs
June 07, 2011, 02:38 PM
FrankDutch
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.
Frank
prod: WF 7.6.10 platform Windows, databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7 test: WF 7.6.10 on the same platform and databases,IE7
June 07, 2011, 03:09 PM
Charles Richards
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
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!
WebFOCUS 7.6 Windows, All Outputs
June 07, 2011, 03:40 PM
FrankDutch
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
Frank
prod: WF 7.6.10 platform Windows, databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7 test: WF 7.6.10 on the same platform and databases,IE7
June 07, 2011, 04:19 PM
Charles Richards
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 ?
WebFOCUS 7.6 Windows, All Outputs
June 07, 2011, 04:47 PM
FrankDutch
if you do a BY HIGHEST TOTCOST NOPRINT whitin the comclass it will work that way
Frank
prod: WF 7.6.10 platform Windows, databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7 test: WF 7.6.10 on the same platform and databases,IE7