Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Calculation in SubTotal row?

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Calculation in SubTotal row?
 Login/Join
 
Gold member
posted
I'm trying to calculate margin by salesperson for a given period of time. When I try to summarize the average margin for a given salesperson, I can't get the calculation to come out correctly using AVE., SUBTOTAL, RECOMPUTE, etc.

Problem is, some tickets don't have any dollars associated with them so don't have a margin. When using the AVE. function, it includes those ZERO lines in the division of the total number of lines so it throws the average off.

I'd like to be able to just use the "MARPER" formula in the SUBTOTAL line, but I'm sure there is another way to do it.

Here's my code:

DEFINE FILE MONTHLY_MARGIN_BY_SALESPERSON_INSIDE_VW
MARPER/D11.2B%S = ((EXT_SALES_DOLLARS - EXT_ACT_COST)/EXT_SALES_DOLLARS)*100;
END

SET SUMMARYLINES=NEW
TABLE FILE MONTHLY_MARGIN_BY_SALESPERSON_INSIDE_VW
PRINT

PL_DESCRIPTION AS 'Product Line'
CUSTOMER_NAME AS 'Customer'
JOB_DESCRIPTION AS 'Job Address'
ORDER_NUM AS 'Ticket #'
ITEM_NUM AS 'SKU'
ITEM_DESCRIPTION AS 'Item Description'
INVOICE_DATE AS 'Invoice Date'
EXT_SALES_DOLLARS/D11BM AS 'Actual,Sales'
MARPER AS 'Margin,Act %'

BY SALESPERSON_NAME NOPRINT
BY CUSTOMER_NAME NOPRINT
BY MARPER NOPRINT
ON SALESPERSON_NAME SUBTOTAL EXT_SALES_DOLLARS



Thanks for any suggestions.

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



WebFOCUS 8.1.05
Windows, All Outputs
 
Posts: 67 | Registered: May 21, 2014Report This Post
Expert
posted Hide Post
Try this: Include a check for the existance of "tickets" in your "MARPER/D11.2B%S = ((EXT_SALES_DOLLARS - EXT_ACT_COST)/EXT_SALES_DOLLARS)*100;" statement.
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report This Post
Gold member
posted Hide Post
Doug,

Not sure I understand what you mean...

MARPER calculates correctly for each individual row (ticket). Calculating the overall margin percentage (MARPER) by salesperson and getting it to show in the subtotal of the MARPER column is where I'm having a problem.

Does that not make sense?



WebFOCUS 8.1.05
Windows, All Outputs
 
Posts: 67 | Registered: May 21, 2014Report This Post
Master
posted Hide Post
I think Doug is suggesting that you exclude the records where there is no margin from the calculation and only include the ones you want.


WebFOCUS 7.7.05 Windows, Linux, DB2, IBM Lotus Notes, Firebird, Lotus Symphony/OpenOffice. Outputs PDF, Excel 2007 (for OpenOffice integration), WP
 
Posts: 674 | Location: Guelph, Ontario, Canada ... In Focus since 1985 | Registered: September 28, 2010Report This Post
Expert
posted Hide Post
Yes George, That's my point. While there's other ways to do it, that should work. Exclude those rows without tickets.
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report This Post
Gold member
posted Hide Post
Thank you for the clarification. I already asked the people that consume this report and they would prefer to see ALL of the tickets, including the ones with $0.

Any suggestions on the "other ways" I can accomplish this?

Thanks.



WebFOCUS 8.1.05
Windows, All Outputs
 
Posts: 67 | Registered: May 21, 2014Report This Post
Master
posted Hide Post
All you have to do is create another field with a DEFINE and add that as another column to your report. Or do the calculation with a NOPRINT and then put the result in a SUBFOOT. I do exactly this all the time.


WebFOCUS 7.7.05 Windows, Linux, DB2, IBM Lotus Notes, Firebird, Lotus Symphony/OpenOffice. Outputs PDF, Excel 2007 (for OpenOffice integration), WP
 
Posts: 674 | Location: Guelph, Ontario, Canada ... In Focus since 1985 | Registered: September 28, 2010Report This Post
Gold member
posted Hide Post
Thanks for the reply George.

Wouldn't this be the same thing I'm already doing with the "MARPER" column? And if not, how do I tell it to only calculate the formula on the SUBTOTAL line for each salesperson instead of for each individual row?

Everything I've tried so far sums all of the MARPER values from each row.

Thanks



WebFOCUS 8.1.05
Windows, All Outputs
 
Posts: 67 | Registered: May 21, 2014Report This Post
Virtuoso
posted Hide Post
Assuming you want overall margin (which is equivalent to weighted average of the margins, using sales as the weighting factor), I think the following approach will yield the result you want, without need to resort to SUBFOOT:

compute sales/D12=EXT_SALES_DOLLARS ;
compute cost/D12=if sales eq 0 then 0 else EXT_ACT_COST ;
compute margin/D11.2B%S=if sales eq 0 then 0 else 100*(sales-cost)/sales ;

on table summarize


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Master
posted Hide Post
I see what Jack is doing - eliminating the cost (assuming there is one) and thereby negating the effect of the zero sales value on the calculation.

My suggestion is for a separate column that you set up using a define that ONLY calculates the margin on where the sales value is greater than zero.

So you would end up with something like this:
            Cost    Sale  Markup  Margin
Sale #1     10.00   12.00   2.00  16.67%
Sale #2     10.00    0.00   0.00   
Sale #3      0.00    0.00   0.00
Sale #4     10.00   15.00   5.00  33.34%


You will have to use a RECOMPUTE on SalesRep to use this method.

Or, as I said, just use a multi-verb request with a subfoot for each sales rep.

Here's one of mine (not separated by slesrep, but it could be easily done).
DEFINE FILE HOLD
    YEAR/YY = BLDATE;
    NUMORDERS/A20 = INVNUM;
    MCOST_FOBOT/P10.2 = COST_FOBOT;
    RCOST_FOBOT/P12.2C = COST_FOBOT;
    RFOBOT/P12.2C =  FOBOT;
    MFOBOT/P12.2C =  FOBOT;
    RCIFOT/P12.2C =  CIFOT;
    MPROFIT/P9.2 =  PROFIT;
    RPROFIT/P12.2C = PROFIT;
END


TABLE FILE HOLD
-* FIRST COMPUTE REPORT TOTALS
-* NEXT COMPUTE MONTHLY TOTALS
-* FINALLY COMPUTE INDIVIDUAL ORDERS
SUM
     HOLD.HOLD.RCIFOT NOPRINT
     HOLD.HOLD.RFOBOT NOPRINT
     HOLD.HOLD.RPROFIT NOPRINT
     HOLD.HOLD.RCOST_FOBOT NOPRINT
     COMPUTE AVRCOM/F5.2 = ( RPROFIT / RFOBOT ) * 100; NOPRINT
SUM
     HOLD.HOLD.MPROFIT NOPRINT
     HOLD.HOLD.MFOBOT NOPRINT
     CNT.HOLD.HOLD.INVNUM AS '# OF,ORD'
     COMPUTE AVMCOM/F5.2 = ( MPROFIT / MFOBOT ) * 100; AS 'MTH,AVG,PCT'
BY  HOLD.HOLD.YEAR NOPRINT
BY  HOLD.HOLD.YEARMTH NOPRINT
BY  HOLD.HOLD.MONTH AS 'MONTH'
PRINT
     HOLD.HOLD.BLDATE AS 'SHIPPING, DATE'
     HOLD.HOLD.CCODE AS 'CUST'
     HOLD.HOLD.OSTAT AS 'S,T'
     HOLD.HOLD.CIFOT AS ' CIF,ORDER,TOTAL'
     HOLD.HOLD.FOBOT AS ' FOB,ORDER,TOTAL'
     HOLD.HOLD.COST_FOBOT AS ' FOB,ORDER,COST'
     HOLD.HOLD.PROFIT AS 'PROFIT'
     HOLD.HOLD.PCTPROFIT AS 'PCT'
     HOLD.HOLD.TOOMUCH AS ' '
BY  HOLD.HOLD.YEAR NOPRINT
BY  HOLD.HOLD.YEARMTH NOPRINT
BY  HOLD.HOLD.MONTH AS 'MONTH'
BY  HOLD.HOLD.INVNUM AS 'INVOICE,NUMBER'

ON HOLD.HOLD.YEAR SUBHEAD
" <YEAR "
ON HOLD.HOLD.YEAR PAGE-BREAK

ON HOLD.HOLD.MONTH SUBTOTAL
     AVMCOM
     'HOLD.HOLD.CIFOT'
     'HOLD.HOLD.FOBOT'
     'HOLD.HOLD.COST_FOBOT'
     'HOLD.HOLD.PROFIT' AS 'Total for '
ON TABLE SUBHEAD
"DIRECT SALES MONTHLY SHIPPING REPORT"
"   "
"Average profit for period: <AVRCOM "
" Report Date <+0>&DATEtMDYY <+0> "
ON TABLE SUBFOOT
"GRAND TOTALS"
"                       CIF      <RCIFOT>"
"                      FOB     <RFOBOT>"
"                   COST     <RCOST_FOBOT>"
" GROSS PROFIT    <RPROFIT>"
"    AVERAGE PCT    <AVRCOM>"
" "
"Notes:           Profit is calculated as FOB order total - FOB order Cost."
"                      Percent profit is (Profit / FOB order total) x 100 "
" "
" This report runs from the file &FOCFEXNAME "
ON TABLE NOTOTAL
END


WebFOCUS 7.7.05 Windows, Linux, DB2, IBM Lotus Notes, Firebird, Lotus Symphony/OpenOffice. Outputs PDF, Excel 2007 (for OpenOffice integration), WP
 
Posts: 674 | Location: Guelph, Ontario, Canada ... In Focus since 1985 | Registered: September 28, 2010Report This Post
Gold member
posted Hide Post
Thank you very much for the replies guys. The ideas were very helpful!

I was able to get what I needed in the Excel output by doing something pretty close to what I already had. I don't know I had it figured correctly before and just wasn't looking at it right... or whether I finally got it to work accidentally.

What I ended up with was:

DEFINE FILE MONTHLY_MARGIN_BY_SALESPERSON_INSIDE_VW
MARPER/D11.2B%S = ((EXT_SALES_DOLLARS - EXT_ACT_COST)/EXT_SALES_DOLLARS)*100;
END

SET SUMMARYLINES=NEW
TABLE FILE MONTHLY_MARGIN_BY_SALESPERSON_INSIDE_VW
PRINT

PL_DESCRIPTION AS 'Product Line'
CUSTOMER_NAME AS 'Customer'
JOB_DESCRIPTION AS 'Job Address'
ORDER_NUM AS 'Ticket #'
ITEM_NUM AS 'SKU'
ITEM_DESCRIPTION AS 'Item Description'
INVOICE_DATE AS 'Invoice Date'
EXT_SALES_DOLLARS/D11.2BM AS 'Actual,Sales'
EXT_ACT_COST/D11.2BM AS 'Actual,Cost'
COMPUTE MARGIN/D11.2B%S = ((EXT_SALES_DOLLARS - EXT_ACT_COST)/EXT_SALES_DOLLARS)*100; AS 'Actual,Margin'


BY SALESPERSON_NAME NOPRINT
BY CUSTOMER_NAME NOPRINT
BY MARPER NOPRINT
ON SALESPERSON_NAME SUBTOTAL EXT_SALES_DOLLARS EXT_ACT_COST RECOMPUTE MARGIN

Thanks again for the suggestions. Being fairly new to WebFOCUS, it definitely helps to see different ways of doing things.



WebFOCUS 8.1.05
Windows, All Outputs
 
Posts: 67 | Registered: May 21, 2014Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Calculation in SubTotal row?

Copyright © 1996-2020 Information Builders