Dear Gurus,

I need to create a summary report that needs both row and column average that involves Null and other numbers.
Ex. Year
Name 2001 2002 Avg
John NULL 10 5
Joe 10 10 10

Avg 5 10 7.5

For some reasons, WebFocus would not include Null in average calculation, it gave a result like:

Ex. Year
Name 2001 2002 Avg
John NULL 10 10
Joe 10 10 10

Avg 10 10 10

My code is like:

TABLE FILE SLS14
SUM AVE.MTHCUSTAVG AS 'Customer Average' BY PAYERINFO
ACROSS NEWMTH NOPRINT
ACROSS NEWMTHNAM AS 'Month'
COMPUTE All_Cust_Avg/P13.2 = C1;
ON TABLE SUMMARIZE AVE.
END


Is there a way to force WebFocus to include No Value/Null in average calculation?

Thank you so much for your help!

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

Did you try using a DEFINE:

  
DEFINE FILE SLS14
MTHCUSTAVG/I9S = IF MTHCUSTAVG IS MISSING THEN 0 ELSE MTHCUSTAVG;
END

before your TABLE command?

Dear Daniel,

Thank you for your response and help!

I did try to define it, but it did not work for me since the report is ACROSS using Month. For a specific month, those customers do not have any data. As a result, it showed nothing no matter how I defined.

Try this istead:

MTHCUSTAVG/I9S MISSING ON= IF MTHCUSTAVG IS MISSING THEN MISSING ELSE MTHCUSTAVG;
  

Nulls are usually displayed as a dot and are used in the calculation. From you output sample above, I am assuming that the word NULL was just for emphasis.

If it is the word NULL, then no WF cannot use that in calculations.

Dear Mary,

Thank you for your input and help!

My data are like the following:
John(Name), 02(Month), Feb(Month), 10(MTHCUSTAVG, a calcualted field). Some data do not have any data say in Feb. When I use ACROSS Month, WebFocus will not put in anything in the field if there is nothing to show. When calculating the average, WebFocus will ignore that Blank and treat it like it never there. As a result, the average will be off.

Thanks BBKONG for further explaining what was needed.

Using the CAR file, I was able to get the TOTALs to RECOMPUTE the AVE.SEATS.

First I had to hold the file with "forcing" the missing values using the BY ROWS logic.

SET NODATA = 0
SET ASNAMES=ON,HOLDLIST=PRINTONLY

TABLE FILE CAR
SUM
AVE.SEATS/P13.2 AS 'SEATS'
BY COUNTRY
BY CAR
ROWS 'ALFA ROMEO'
OVER 'ROMEO'
OVER 'AUDI'
OVER 'BMW'
OVER 'DATSUN'
OVER 'JAGUAR'
OVER 'JENSEN'
OVER 'MASERATI'
OVER 'PEUGEOT'
OVER 'TOYOTA'
OVER 'TRIUMPH'
ON TABLE HOLD AS HLDCAR 
END
-RUN

TABLE FILE HLDCAR
SUM
SEATS
BY COUNTRY
ACROSS E02 AS CAR
ON TABLE RECOMPUTE AVE.SEATS
END  

Try this CAR file example.

By adding the HOLDMISS setting to Mary's excellent example (with or without the NODATA setting), the missing values are carried into the HOLD file and, I believe, thereby gives a better example of the problem faced by BBKONG.

SET HOLDMISS = ON
-*SET NODATA = 0
SET ASNAMES=ON,HOLDLIST=PRINTONLY
-*
TABLE FILE CAR
SUM
AVE.SEATS/P13.2 AS 'SEATS'
BY COUNTRY
BY CAR
ROWS 'ALFA ROMEO'
OVER 'ROMEO'
OVER 'AUDI'
OVER 'BMW'
OVER 'DATSUN'
OVER 'JAGUAR'
OVER 'JENSEN'
OVER 'MASERATI'
OVER 'PEUGEOT'
OVER 'TOYOTA'
OVER 'TRIUMPH'
ON TABLE HOLD AS HLDCAR
END

TABLE FILE HLDCAR
 SUM SEATS
 BY COUNTRY
 ACROSS E02 AS ''
 ON TABLE RECOMPUTE AVE.
END  

Under these circumstances, Danny's DEFINE suggestion should fix the column averages problem. The original post shows that BBKONG may also be trying to calculate row averages. To accomplish this, I changed Mary's code to the multi-verb request below and manually calculated the row averages.

SET HOLDMISS = ON
SET ASNAMES=ON,HOLDLIST=PRINTONLY
-*
TABLE FILE CAR
 SUM AVE.SEATS/P13.2 AS 'SEATS'
 BY COUNTRY
 BY CAR
 ROWS 'ALFA ROMEO'
 OVER 'ROMEO'
 OVER 'AUDI'
 OVER 'BMW'
 OVER 'DATSUN'
 OVER 'JAGUAR'
 OVER 'JENSEN'
 OVER 'MASERATI'
 OVER 'PEUGEOT'
 OVER 'TOYOTA'
 OVER 'TRIUMPH'
 ON TABLE HOLD AS HLDCAR
END
-*
DEFINE FILE HLDCAR
 SEATSX/P13.2 = IF (SEATS IS MISSING) THEN 0 ELSE SEATS ;
END
-*
TABLE FILE HLDCAR
 SUM SEATSX NOPRINT
     CNT.SEATSX NOPRINT
     COMPUTE CAR_AVG/P13.2 = SUM.SEATSX / CNT.SEATSX ; NOPRINT
 BY COUNTRY
-*
 SUM SEATSX
 BY COUNTRY
 ACROSS E02 AS ''
 COMPUTE All_Car_Avg/P13.2 = CAR_AVG ;
 ON TABLE RECOMPUTE AVE.
END  

Wow! Looks good Dan. Thanks for taking it the next step further.

Dear Dan and Mary,

Thank you so much for those nice codes and explanations. I learned a lot from your replies.

I tries both of your methods, but I still could not get the result I need. I guess I either did it wrong or I need different method to get what I need.

I was trying to get both row and column average(the NEWMTH is a variable, could have one month or multiple months). My blank field created after my ACROSS run, see blow

TABLE FILE SLS14
SUM AVE.MTHCUSTAVG AS 'Customer Average' BY PAYERINFO
ACROSS NEWMTH NOPRINT
ACROSS NEWMTHNAM AS 'Month'
COMPUTE All_Cust_Avg/P13.2 = C1;
ON TABLE RECOMPUTE AVE.
END

If I can put the data using SET NODATA = 0 or SET HOLDMISS = ON in a new hold table and recompute the average, I can get the column average. Using COMPUTE for row average did not work for me in the new hold table. At the same time, I lost control of the field name because the data was brought in from a ACROSS table.

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

Don't use ACROSS when creating your HOLD file. Use BY instead. Use ACROSS only when producing your final output.

Using Dan's example, yours would look something like this.

You would need to do the HOLD file first with BY fields "forcing" the data for the MTHs.

Then use the ACROSS logic.

You wouldn't want the HOLDMISS setting ON as you don't want missing data in your hold file.

The NODATA setting is primarily for display purposes.

 

TABLE FILE SLS14
 SUM AVE.MTHCUSTAVG AS 'Customer Average'
 BY PAYERINFO
 BY NEWMTH
 BY NEWMTHNAM
 ROWS 'JAN'
 OVER 'FEB'
 OVER 'MAR'
 OVER 'APR'
 OVER 'MAY'
 OVER 'JUN'
 OVER 'JUL'
 OVER 'AUG'
 OVER 'SEP'
 OVER 'OCT'
 OVER 'NOV'
 OVER 'DEC'
 ON TABLE HOLD AS HLDSLS14
END
-*
DEFINE FILE HLDSLS14
 MTHCUSTAVX/P13.2 = IF (MTHCUSTAVG IS MISSING) THEN 0 ELSE MTHCUSTAVG ;
END
-*
TABLE FILE HLDSLS14
 SUM MTHCUSTAVX NOPRINT
     CNT.MTHCUSTAVX NOPRINT
     COMPUTE CUST_AVG/P13.2 = SUM.MTHCUSTAVX / CNT.MTHCUSTAVX ; NOPRINT
 BY COUNTRY
-*
 SUM MTHCUSTAVX
 BY PAYERINFO
 ACROSS E03 AS ''
 COMPUTE All_Cust_Avg/P13.2 = CUST_AVG ;
 ON TABLE RECOMPUTE AVE.
END


 

Dear Dan,

Thank you for your quick response.

My data is little bit complicated. In my data, I have 4 records under two different customers.
Each record has different NEWMTH info. For one customer, only one recrod for Feb. Another has three records under three differnt months.

Special thanks to Dan and Mary. I learned a lot from your ideas.

I finally figured out something to go around it. The following is the code I used:


TABLE FILE SLS12
SUM PYRCNTR PAYERINFO AS PAYER CLRDTERMDAYS AS CLRDAYS
COMPUTE CUSTAVG/P13.1 = CLRDTERMDAYS / PYRCNTR;
BY PAYERINFO
BY PERIOD
ON TABLE HOLD AS SLS13
END
-*
DEFINE FILE SLS13
MTHCNTR/P13 = &FOWMTH;
END
-*
TABLE FILE SLS13
SUM AVE.CUSTAVG AS ''
BY PAYERINFO
ACROSS PERIOD AS ''
ON TABLE HOLD AS SLS14 FORMAT ALPHA
END
-*
TABLE FILE SLS14
PRINT * ROW-TOTAL/P13.1 AS 'T'
BY PAYERINFO NOPRINT
ON TABLE HOLD AS SLS15
END
-*
TABLE FILE SLS15
PRINT *
COMPUTE Customer_Avg/P13.1 = T / &FOWMTH;
ON TABLE HOLD AS SLS16
END
-*
TABLE FILE SLS16
PRINT *
BY PAYERINFO NOPRINT
ON TABLE RECOMPUTE AVE. AS 'Monthly_Avg'
ON TABLE PCHOLD FORMAT EXL2K