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,

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.

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?

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.

Yes George, That's my point. While there's other ways to do it, that should work. Exclude those rows without tickets.

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.

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.

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

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

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

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.