Subtotals and Total Calculations in a SubHeader - TOTMAX, TOTMIN, TOT

[Tracy Rue]

Alright, I've got a multi-verb request and I've made most of the calculations I need in the header work. However, I cannot get the % fields to total up properly.

I calculate gross margin and the margin for Spot and Forward Trade types in the report using computes:

COMPUTE Gross_Margin/D12.2% = TRADESALESVIEW.MG_TRANSACTIONS.DEALTRANSSPREADBASE / TradingVolumeExcl121 * 100; COMPUTE FwdMargin/D12.2% = IF TRADESALESVIEW.DIM_DEAL.DEALTYPENAME EQ 'Fixed Dated Forward' OR 'Window Forward' THEN GrossMarginDefined ELSE 0; COMPUTE SpotMargin/D12.2% = IF TRADESALESVIEW.DIM_DEAL.DEALTYPENAME EQ 'Spot' THEN GrossMarginDefined ELSE 0;

Problem 1: I don't actually want the ELSE part of my statement; I want it to return nothing if it's not that trade type so that it doesn't affect my average calculation for the column. I've tried ELSE MISSING but no bueno. What's the right way to return nothing?

Problem 2: I can't get the average calculations for the 3 columns into the SubHeader. My subtotal is functioning (aside from Problem 1) but using TOT. or TOTMAX. with any of the computed % fields does not return the right values. As I said, I've got a multi-verb request so I should be able to pull the report total for the 3 columns, but I think I need to solve problem 1 before I can even get the right value in the header... Here's the whole thang in order to make sense of it:

-********************************************************************************************-* Report-********************************************************************************************TABLE FILE DATAMART/TRADESALESVIEW -**********************-*CUSTOMER LEVEL SUMS:-**********************SUM  TransferAmount121 AS '1-to-1,Amount' NOPRINT TRADESALESVIEW.MG_TRANSACTIONS.FXVOLUMEEXCL121/D20.2C AS 'FX,Volume' NOPRINT COMPUTE Total_Gross_Margin/D12.2% = TRADESALESVIEW.MG_TRANSACTIONS.DEALTRANSSPREADBASE / TradingVolumeExcl121 * 100; AS 'GM,%' NOPRINT COMPUTE Total_Fwd_Margin/D12.2% = FwdMarginDefined; COMPUTE Total_Spot_Margin/D12.2% = SpotMarginDefined; COMPUTE Longev/I9 = TRADESALESVIEW.TODAYS_DATE.CURRENT_DATE - TRADESALESVIEW.DIM_CUSTOMER_FT_DATE.FULLDATE; NOPRINT AS 'Longevity' COMPUTE LongevWeeks/I9 = ( TRADESALESVIEW.DIM_CUSTOMER_LT_DATE.FULLDATE - TRADESALESVIEW.DIM_CUSTOMER_FT_DATE.FULLDATE ) / 7; NOPRINT AS 'LongevInWeeks' COMPUTE Recency/I9 = TRADESALESVIEW.TODAYS_DATE.CURRENT_DATE - TRADESALESVIEW.DIM_CUSTOMER_LT_DATE.FULLDATE; NOPRINT AS 'Recency' COMPUTE ActivitySpan/I9 = Longev - Recenc; NOPRINT AS 'Active' COMPUTE AcctSpanMonths/I9 = ( Longev - Recenc ) / 30; NOPRINT BY LOWEST 1 TRADESALESVIEW.DIM_CUSTOMER_ADDRESS.CUSTOMERADDRESSID NOPRINT -*****************************-*DEAL LEVEL SUMS:-***************************** SUM TRADESALESVIEW.DIM_DEAL_BASE_CURRENCY.CURRENCYCODE AS 'BASECURR' TRADESALESVIEW.DIM_TRANSACTIONS_BUY_CURRENCY.CURRENCYCODE AS 'BUYCURR' TRADESALESVIEW.DIM_TRANSACTIONS_SELL_CURRENCY.CURRENCYCODE AS 'SELLCURR' TRADESALESVIEW.MG_TRANSACTIONS.DEALTRANSBUYAMOUNT/D20.2C TRADESALESVIEW.MG_TRANSACTIONS.DEALTRANSSELLAMOUNT/D20.2C TransferAmount121 ServiceCharge Total_Gross_Margin Total_Fwd_Margin Total_Spot_Margin GrossMarginDefined COMPUTE FX_VOL/D20.2C = TRADESALESVIEW.MG_TRANSACTIONS.FXVOLUMEEXCL121; COMPUTE Gross_Margin/D12.2% = TRADESALESVIEW.MG_TRANSACTIONS.DEALTRANSSPREADBASE / TradingVolumeExcl121 * 100; COMPUTE FwdMargin/D12.2% = IF TRADESALESVIEW.DIM_DEAL.DEALTYPENAME EQ 'Fixed Dated Forward' OR 'Window Forward' THEN GrossMarginDefined ELSE 0; COMPUTE SpotMargin/D12.2% = IF TRADESALESVIEW.DIM_DEAL.DEALTYPENAME EQ 'Spot' THEN GrossMarginDefined ELSE 0; COMPUTE CADVolume/D20.2C = TRADESALESVIEW.MG_TRANSACTIONS.FXVOLUMEEXCL121CADEQUIVALENT; TRADESALESVIEW.MG_TRANSACTIONS.DEALTRANSSPREADCAD/D20.2C COMPUTE GrossProfit/D12.2C = TRADESALESVIEW.MG_TRANSACTIONS.DEALTRANSSPREADCAD; COMPUTE OneToOnes/I9 = IF GrossMarginDefined EQ 0 AND ServiceCharge EQ 'No' THEN 1 ELSE 0; COMPUTE Fx_Trades/I9 = IF GrossMarginDefined NE 0 THEN 1 ELSE 0; COMPUTE DlCnt/I9 = TRADESALESVIEW.MG_DEAL.DEALCOUNT; COMPUTE Frequency/I9 = ActivitySpan / TRADESALESVIEW.MG_DEAL.DEALCOUNT - 1 ; COMPUTE Longev/I9 = TRADESALESVIEW.TODAYS_DATE.CURRENT_DATE - TRADESALESVIEW.DIM_CUSTOMER_FT_DATE.FULLDATE; COMPUTE LongevWeeks/I9 = ( TRADESALESVIEW.DIM_CUSTOMER_LT_DATE.FULLDATE - TRADESALESVIEW.DIM_CUSTOMER_FT_DATE.FULLDATE ) / 7; COMPUTE LongevYears/D12.2=LongevDays/365; COMPUTE Recency/I9 = TRADESALESVIEW.TODAYS_DATE.CURRENT_DATE - TRADESALESVIEW.DIM_CUSTOMER_LT_DATE.FULLDATE; COMPUTE ActivitySpan/I9 = Longev - Recenc; COMPUTE AcctSpanMonths/I9 = ( Longev - Recenc ) / 30;  MAX.TRADESALESVIEW.DIM_CUSTOMER.CUSTOMERLEGALNAME MAX.TRADESALESVIEW.DIM_CUSTOMER_FT_DATE.FULLDATE AS 'FTDATE' MAX.TRADESALESVIEW.DIM_CUSTOMER_LT_DATE.FULLDATE AS 'LTDATE' MAX.TRADESALESVIEW.DIM_CUSTOMER.FXOENTITYNOALPHA MAX.TRADESALESVIEW.DIM_CUSTOMER.CUSTOMERTYPE MAX.CustomerCurr MAX.TRADESALESVIEW.DIM_CUSTOMER.CUSTOMERNOB MAX.TRADESALESVIEW.DIM_CUSTOMER.CUSTOMERENTITYCLASS MAX.TRADESALESVIEW.DIM_CUSTOMER_CURRENT_REL_MANAGER.RELMGRNAME MAX.CustomerIsOnline BY LOWEST 1 TRADESALESVIEW.DIM_CUSTOMER_ADDRESS.CUSTOMERADDRESSIDBY TRADESALESVIEW.DIM_DEAL_DATE.FISCALYEARBY LOWEST TRADESALESVIEW.DIM_DEAL_DATE.FULLDATE/MDYYBY TRADESALESVIEW.DIM_DEAL.SUBINVORGNAMEBY TRADESALESVIEW.DIM_DEAL.DEALTYPENAMEBY TRADESALESVIEW.DIM_DEAL.DEALNO  WHERE ( TRADESALESVIEW.DIM_CUSTOMER.CUSTOMERENTITYNUMBER EQ &CUSTOMERENTITYNUMBER.(FIND TRADESALESVIEW.DIM_CUSTOMER.CUSTOMERENTITYNUMBER IN TRADESALESVIEW).CUSTOMERENTITYNUMBER:. );WHERE ( TRADESALESVIEW.DIM_DEAL.DEALTYPENAME EQ &DEALTYPENAME.(OR(FIND TRADESALESVIEW.DIM_DEAL.DEALTYPENAME IN DATAMART/TRADESALESVIEW|FORMAT=A50V)).Deal Types:. );  ON TABLE HOLD AS TFINAL FORMAT ALPHAON TABLE SET ASNAMES ONON TABLE SET HOLDATTR ON END-RUN  TABLE FILE TFINAL SUM BASECURR AS 'Deal,Base' BUYCURR AS 'Buy,Curr' SELLCURR AS 'Sell,Curr' TradingVolumeExcl121/D20.2C AS 'Trading,Volx121' NOPRINT DEALTRANSBUYAMOUNT/D20.2C AS 'Buy,Amount' DEALTRANSSELLAMOUNT/D20.2C AS 'Sell,Amount' TransferAmount121/D20.2C AS '1-to-1,Amount' ServiceCharge AS 'Serv,Chg' FX_VOL AS 'FX,Volume' NOPRINT CADVolume AS 'FX Vol,CAD Equiv' DEALTRANSSPREADCAD/D20.2C AS 'GP,CAD Equiv' GrossProfit AS 'Gross,Profit' NOPRINT  GrossMarginDefined AS 'GM%' FwdMargin AS 'Fwd,Margin' SpotMargin AS 'Spot,Margin'  Total_Fwd_Margin NOPRINT Total_Spot_Margin NOPRINT  OneToOnes AS '1to1,Deal' Fx_Trades AS 'FX,Deal'  DlCnt AS 'Deal,Count' NOPRINT Total_Gross_Margin NOPRINT Frequency NOPRINT Recency NOPRINT LongevWeeks NOPRINT LongevYears NOPRINT CustomerIsOnline NOPRINT BY FISCALYEAR AS 'Fiscal,Year'BY LOWEST FULLDATE AS 'Deal,Date'BY SUBINVORGNAME AS 'Branch'BY DEALTYPENAME AS 'Deal,Type'BY DEALNO AS 'Deal,Number' ON FISCALYEAR RECOMPUTE TradingVolumeExcl121 DEALTRANSBUYAMOUNT DEALTRANSSELLAMOUNT TransferAmount121 CADVolume DEALTRANSSPREADCADAVE. GrossMarginDefined AVE. FwdMargin AVE. SpotMargin SUM. OneToOnes SUM. Fx_Trades SUM. DlCnt AS '*TOTAL for FY'  ON TABLE SUBHEAD"Date:<CURRENT_DATE <+0> <+0> <+0> <+0> <+0> ""FxO Legal Name:<CUSTOMERLEGALNAME<+0>Client's First Trade:<FTDATE<+0>Total Vol (CAD)<TOT.CADVolume<+0>Total Number of Trades:<+0><TOT.DlCnt""Entity #:<FXOENTITYNOALPHA<+0>Client's Latest Trade:<LTDATE<+0>High:<TOTMAX.CADVolume<+0>Number of FX Trades:<TOT.Fx_Trades""Client Type:<CUSTOMERTYPE<+0>Trade Recency -days:<Recency<+0>Low:<TOTMIN.CADVolume<+0>Number of 1-1 Trades:<TOT.OneToOnes""Home Currency:<CustomerCurr<+0>Trade Frequency -days:<Frequency <+0> <+0> <+0> ""Industry:<CUSTOMERNOB <+0> <+0>Total GP (CAD):<TOT.GrossProfit<+0>Average Margin-All:<Total_Gross_Margin""Tier:<CUSTOMERENTITYCLASS<+0>Acct Age -weeks:<LongevWeeks<+0>High:<TOTMAX.DEALTRANSSPREADCAD<+0>Avg Margin-Fwds:<TOTAVE.FwdMargin""Current Trader:<RELMGRNAME<+0>Acct Age -years:<LongevYears<+0>Low:<TOTMIN.DEALTRANSSPREADCAD<+0>Avg Margin-Spots:<TOTAVE.SpotMargin""FxO Online Access:<CustomerIsOnline <+0> <+0> <+0> <+0> <+0> "" <+0> <+0> <+0> <+0> <+0> <+0> <+0> "  ON TABLE RECOMPUTE TradingVolumeExcl121 DEALTRANSBUYAMOUNT DEALTRANSSELLAMOUNT TransferAmount121 CADVolume DEALTRANSSPREADCADAVE. GrossMarginDefined AVE. FwdMargin AVE. SpotMargin SUM. OneToOnes SUM. Fx_Trades SUM. DlCnt

Hoping this is actually quite simple as I'm not an advanced developer. The documentation on headers is pretty light but if you can point me to some I'm happy to dive in. Thanks in advance!

[David Beagan]

For your first question, I think you need to specify MISSING ON to allow the COMPUTE or DEFINE to have missing valus. For example,

DEFINE FILE ggsales VAL1/I11 MISSING ON = IF CITY EQ 'Boston' OR 'New York' THEN 1 ELSE MISSING; END TABLE FILE ggsales PRINT CITY VAL1 COMPUTE VAL2/I11 MISSING ON = IF CITY NE 'Boston' OR 'New York' THEN 1 ELSE MISSING; WHERE SEQ_NO LE 10 END

The output looks like the following, with the periods indicating missing values.

[David Beagan]

For you second question, could you reporduce the issue with one of the sampe data files such as ggsales or car file?

[Martin Yergeau]

I do agree with David regarding how to use MISSING

As for your second question, a technic that could be used is to use your TFINAL file as a source to calculate your header's total and hold them in another file. Then read that file to have your totals in variable and finally assign those variables in your header.

Here a sample of what I am speaking

TABLE FILE CARSUM COMPUTE INCTOT/P5.2 = (RETAIL_COST - DEALER_COST) / DEALER_COST * 100;ON TABLE HOLD AS TOTINCEND-RUN -READFILE TOTINC-RUN-DEFAULTH &AVGTOT = 0-TYPE &INCTOT TABLE FILE CARSUM RETAIL_COST AS 'Retail,Cost' DEALER_COST AS 'Dealer,Cost' COMPUTE INC/P5.2 = (RETAIL_COST - DEALER_COST) / DEALER_COST * 100; AS 'Increase %'BY COUNTRY AS 'Country'HEADING"Total Increase &INCTOT %"ON TABLE SET PAGE-NUM NOLEADEND-RUN

 I only have one field (variable) in my sample, but you can have as many as you need

[Tracy Rue]

Yes! This solves problem 1. I didn't realize the MISSING ON could go right after the format. Thanks!

[Tracy Rue]

I got it. Thank you to David for the quick and easy solution to Part 1! Using the MISSING ON ensured all averages calculated properly and allowed me to use AVE. in Part 2.

Part 2 I solved with two steps:

a) Removing the Total_Gross_Margin, Total_Fwd_Margin, and Total_Spot_Margin fields from the DEAL LEVEL SUMS section (lines 36-38 in my original code).

b) I removed the TOTAVE. prefix in the SUBHEAD (lines 140 & 141 in my original code) - which ensured the value in the header was just referring to the Total_xxx_Margin fields in the CUSTOMER LEVEL section. Also, in the CUSTOMER section I changed the field to be Computed with an AVE. prefix:

COMPUTE Total_Gross_Margin/D12.2% = AVE.GrossMarginDefined; COMPUTE Total_Fwd_Margin/D12.2% = AVE.FwdMarginDefined; COMPUTE Total_Spot_Margin/D12.2% = AVE.SpotMarginDefined;

So originally I had the Total_xxx_Margin fields in both sections of the multi-verb request because I believed I needed them to be populated in both sections of the multi-verb request in order for them to successfully report in my final report. However it is clear to me now that only the BY phrases are necessary for the continuity in the multi-verb scenario.

Martin, I did have a similar solution to your suggestion when I first started working on this request however it was simplified to the multi-verb and I really wanted to try and solve it without adding another layer. It is still a very viable solution I may use in the future, so thank you for that, as well.