Maybe I am missing something but I am not getting this working properly.

DEFINE FILE CAR
SALES2/D12.2   = SALES;
END

TABLE FILE CAR
SUM
SALES/D12
CNT.SALES
COMPUTE AVERAGE_PRICE/D12 = SUM.SALES / CNT.SALES;
PCT.SALES2
CNT.DST.COUNTRY
BY BODYTYPE
ON TABLE RECOMPUTE
END

I can get most prefixes to work well with column totals, just .AVE does not work well, but with above (RE)COMPUTE it works.
But I do not manage to get a proper column total for CNT.DST. not with ON TABLE COLUMN-TOTAL, not with RECOMPUTE, not with putting it in a straight forward COMPUTE.
It is giving a total of 9 countries in the total line which is a sum of the detail lines. I want total of 5 countries as there are only 5 distinct countries.

Does someone have a solution for this?

I guess the issue is that Webfocus collects only the detail lines from the source table and calculates the totals from the internal matrix, but the distinct values of the countries for each row are not stored in the internal matrix, so it is not possible for Webfocus to recompute it in the column total.


thanks, Martin.

This message has been edited. Last edited by: <Kathryn Henning>,

Hi,

Try this.

APP PATH IBISAMP
-RUN
DEFINE FILE CAR
SALES2/D12.2   = SALES;
BLANK_VAL/A1='';
END

TABLE FILE CAR
SUM
COMPUTE SALES_1/D12=SALES;
COMPUTE C_SALES/I11=CNT.SALES;
COMPUTE AVERAGE_PRICE/D12 = SUM.SALES / CNT.SALES;
COMPUTE PCT_S/D12.2=PCT.SALES2;
COMPUTE C_D_CNTRY/I11=CNT.DST.COUNTRY;
BY BLANK_VAL
BY BODYTYPE
ON TABLE HOLD AS HLD_1
ON TABLE SET HOLDLIST PRINTONLY
END
DEFINE FILE CAR
SALES2/D12.2   = SALES;
BLANK_VAL/A1='';
END

TABLE FILE CAR
SUM
COMPUTE T_SALES_1/D12=SALES;
COMPUTE T_C_SALES/I11=CNT.SALES;
COMPUTE T_AVERAGE_PRICE/D12 = SUM.SALES / CNT.SALES;
COMPUTE T_PCT_S/D12.2=PCT.SALES2;
COMPUTE T_C_D_CNTRY/I11=CNT.DST.COUNTRY;
BY BLANK_VAL
ON TABLE HOLD AS HLD_2
ON TABLE SET HOLDLIST PRINTONLY
END
-RUN
JOIN BLANK_VAL IN HLD_2 TO MULTIPLE BLANK_VAL IN HLD_1 AS J0 
END

-RUN
TABLE FILE HLD_2
PRINT
SALES_1 AS 'SALES'
C_SALES AS 'SALES COUNT'
AVERAGE_PRICE AS 'AVERAGE PRICE'
PCT_S AS 'PCT SALES'
C_D_CNTRY AS 'COUNT DISTINCT COUNTRY'
BY BODYTYPE
ON TABLE SUBFOOT
"TOTAL<T_SALES_1<T_C_SALES<T_AVERAGE_PRICE<T_PCT_S<T_C_D_CNTRY"
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
TYPE=TABFOOTING, HEADALIGN=BODY, JUSTIFY=RIGHT,$
TYPE=TABFOOTING, ITEM=1, COLSPAN=1, JUSTIFY=LEFT,  $
TYPE=TABFOOTING, ITEM=2, COLSPAN=1,  $
TYPE=TABFOOTING, ITEM=3, COLSPAN=1,  $
TYPE=TABFOOTING, ITEM=4, COLSPAN=1,  $
TYPE=TABFOOTING, ITEM=5, COLSPAN=1,  $
TYPE=TABFOOTING, ITEM=6, COLSPAN=1,  $
ENDSTYLE
END

There will be multiple solutions available to get this.

Thanks,
Ram

This might be a little more compact

DEFINE FILE CAR
SALES2/D12.2   = SALES;
END

TABLE FILE CAR
SUM 
     CAR.BODY.SALES/D12
     CNT.CAR.BODY.SALES
     COMPUTE AVERAGE_PRICE/D12 = SUM.SALES / CNT.SALES;
     PCT.CAR.BODY.SALES2/D12.2%
     COMPUTE COUNT_CNTRY/I1 = IF COUNTRY NE LAST COUNTRY THEN 1 ELSE 0;
BY  CAR.BODY.BODYTYPE
ON TABLE COLUMN-TOTAL AS 'TOTAL'
END

Ram, thanks for your solution. It is quite a workaround to get it done, but the solution works.

Alex, I am afraid your solution does not work. In the total it does provide the correct number of countries, but in the detail lines if states 1 country on each line, which is not correct, and I think the total of 5 is just a sum of the five 1's on the detail line, and not a real recompute.

Your solution will work for the detail lines if you make a new define:
COUNT_CNTRY/I2 = IF COUNTRY NE LAST COUNTRY OR BODYTYPE NE LAST BODYTYPE THEN 1 ELSE 0;
and then use a SUM.COUNT_CNTRY
This gives correct detail lines, although in general I assume you have to arrange that the source table is properly sorted. But still this solution does not provide correct recomputed total, but just a sum of the detail values.

Hi Ram,

How do you accomplish this if you have an ACROSS as well and want correct column and across totals?

So the simple CAR example would be:

TABLE FILE CAR
SUM CNT.DST.COUNTRY
BY  BODYTYPE
ACROSS SEATS ACROSS-TOTAL
ON TABLE COLUMN-TOTAL
END  

But these totals are not correct recomputes of the CNT.DST

thanks, Martin.

This is the result of your query.

                SEATS 
                    2           4          5        TOTAL 

BODYTYPE         COUNT       COUNT      COUNT       COUNT 
                 DISTINCT    DISTINCT   DISTINCT    DISTINCT
                 COUNTRY     COUNTRY    COUNTRY     COUNTRY 
CONVERTIBLE         1           0          0            1 
COUPE               1           0          0            1 
HARDTOP             1           0          0            1 
ROADSTER            1           0          0            1 
SEDAN               0           4          3            7 

TOTAL               4           4          3           11 

Now to me these are the correct figures and the correct ACROSS-TOTALS and COLUMN-TOTALS, i.e. 1 Country has a convertible with 2 seats, 4 countries have a sedan with 4 seats etc.

Which figures do you believe are incorrect.

The details are correct, but I would like a recompute of the column-totals and across-totals. I want a report on the number of distinct countries, and as there are only 5 countries in total, no column or across total can be more than 5.

So in the column-totals I would like to have the number of distinct countries per seat value (CNT.DST.COUNTRY ACROSS SEATS) and in the across-totals the number of distinct countries per bodytype (CNT.DST.COUNTRY BY BODYTYPE).

For example the 2-seaters are sold as follows: CONVERTIBLE and COUPE are only sold in ENGLAND, the HARDTOP and ROADSTER are only sold in ITALY, so I would like the column total for 2-seaters to be 2 distinct countries and not add the four 1's.

The correct figures I would like to see are:

                SEATS 
                    2           4          5        TOTAL 

BODYTYPE         COUNT       COUNT      COUNT       COUNT 
                 DISTINCT    DISTINCT   DISTINCT    DISTINCT
                 COUNTRY     COUNTRY    COUNTRY     COUNTRY 
CONVERTIBLE         1           0          0            1 
COUPE               1           0          0            1 
HARDTOP             1           0          0            1 
ROADSTER            1           0          0            1 
SEDAN               0           4          3            5 

TOTAL               2           4          3            5

This message has been edited. Last edited by: Martin vK,

Basically you have 4 different requests to merge here.
1. CNT.DST.COUNTRY
2. CNT.DST.COUNTRY BY SEATS
3. CNT.DST.COUNTRY BY BODYTYPE
4. CNT.DST.COUNTRY BY SEATS BY BODYTYPE

and going ACROSS SEATS in the output. Mmm. Was going to wish you luck , but was bored.

FILEDEF COUNTING DISK COUNTING.FTM
TABLE FILE CAR
SUM 
COMPUTE SEATS/A9 ='Total';
CNT.DST.COUNTRY AS SEATCOUNT
COMPUTE BODYTYPE/A12 ='Total';
ON TABLE SET ASNAMES ON
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD AS COUNTING
END  
FILEDEF COUNTING DISK COUNTING.FTM (APPEND
TABLE FILE CAR
SUM 
CNT.DST.COUNTRY AS SEATCOUNT
COMPUTE BODYTYPE/A12 ='Total';
BY SEATS NOPRINT
BY TOTAL COMPUTE SEATS/A9 =FPRINT(SEATS, 'I9', 'A9');
ON TABLE SET ASNAMES ON
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD AS COUNTING
END  
TABLE FILE CAR
SUM
CNT.DST.COUNTRY AS SEATCOUNT
BODYTYPE 
BY BODYTYPE NOPRINT
BY TOTAL COMPUTE SEATS/A9 ='Total';
ON TABLE SET ASNAMES ON
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD AS COUNTING
END  
TABLE FILE CAR
SUM
CNT.DST.COUNTRY AS SEATCOUNT
BODYTYPE 
BY SEATS NOPRINT
BY BODYTYPE NOPRINT
BY TOTAL COMPUTE SEATS/A9 =FPRINT(SEATS, 'I9', 'A9');
ON TABLE SET ASNAMES ON
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD AS COUNTING
END  
TABLE FILE COUNTING
SUM 
COMPUTE CNTR/I9 =  SEATCOUNT; AS 'COUNT,DISTINCT,COUNTRY'
ACROSS SEATS
BY BODYTYPE
ON TABLE SET NODATA 0
ON TABLE SET PAGE NOLEAD
END

Only thing to be aware of is that the word 'Total' must actually be the highest in the SEATS and BODYTYPE sequence, which in this example it is, but in real life you may have to alter it to get sequence correct.

This message has been edited. Last edited by: Alan B,

Hi Allan,

Excellent, thank you for this.
I just made a minor adjustment.
BY TOTAL COMPUTE SEATS/A9 =FPRINT(MAX.SEATS, 'I9', 'A9');
otherwise it sums the seats.

regards, Martin.