I'm trying to produce a report that looks something like this:

               2017     2018     2019
Group     Category

Grp 1     Cat A       10        7      13
          Cat B       7         16     22
          Cat C       1         2      4
TOTAL Grp 1           14        17     27
Cat

You'll notice that none of the columns add up. That's because I am working with Counts of individuals that could belong to more than one category. So in this example, there are 10 unique individuals in Cat A in 2017; but 3 of those 10 are also counted in Cat B in 2017; and the 1 in Cat C in 2017 is duplicated in both of Cat A and B. So when the total is produced for all of Grp 1, the total should be 14 because there are only 14 unique individuals in Grp 1.

The problem is that I cannot figure out how to get total row to go back and count unique individuals again. I keep getting a sum instead (i.e., Grp 1 Total in 2017 = 18 rather than 14).

The basic code is

 TABLE FILE MY_FILE
SUM CNT.DST.PERSON_ID
BY GROUP
BY CATEGORY 
ACROSS YEARS
ON GROUP RECOMPUTE CNT.DST.PERSON_UID
END 

But, like I said, this gives me the sum for each year, not a new count of distinct PERSON_UIDs. I tried a number of variations on SUBTOTAL, SUMMARIZE, RECAP, RECOMPUTE, etc. to no avail.

So I gave up and tried a different tack: I calculated the GROUP total in a separate table and joined it back in like this:

TABLE FILE MY_FILE
SUM CNT.DST.PERSON_UID AS GRP_TOT
BY GROUP
BY YEARS
ON TABLE HOLD AS MY_TOTALS
END

JOIN CLEAR *
JOIN INNER
     FILE MY_FILE AT GROUP TAG MF
     TO ALL
     FILE MY_TOTALS AT GROUP TAG MT
     AS JNMFMT
     WHERE MF.GROUP EQ MT.GROUP;
     WHERE MF.YEARS EQ MT.YEARS;
END

TABLE FILE MY_FILE
SUM   CNT.DST.PERSON_UID
     LST.GRP_TOT NOPRINT
BY GROUP
BY CATEGORY
ACROSS YEARS
ON GROUP SUBTOTAL LST. GROUP
ON TABLE PCHOLD FORMAT XLSX
END

The problem with this is the NOPRINT on LST.GRP_TOT. I cannot see the subtotal either. If I remove the NOPRINT, then I get what I want except for it showing the group total on every single row in the report.

I know I've seen an elegant solution to this somewhere; but it is Friday afternoon; and it is not coming to me. Do any of you colleagues have suggestions? Thanks in advance.

This message has been edited. Last edited by: FP Mod Chuck,

How about something like this:

FILEDEF REPDATA DISK repdata.ftm (APPEND

TABLE FILE GGSALES
COUNT 
  DST.UNITS
   BY ST
   BY PRODUCT 
ON TABLE HOLD AS repdata
END

TABLE FILE GGSALES
COUNT 
COMPUTE PRODUCT/A16='|Total';
    DST.UNITS 
     BY ST
ON TABLE HOLD AS repdata
END

TABLE FILE REPDATA
   SUM UNITS
    BY PRODUCT
ACROSS ST
END

We queried the table twice then put the subtotals in the report footer.

-*xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
-* HOLD File - DIVISIONNAME_SUBTOTAL
-*xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
-* Accounts are a count distinct and must be calculated on the correct grain.
-* An account can cross multiple segments, so it cannot be subtotaled in the report.
SET ASNAMES = ON
SET HOLDATTR = ON
TABLE FILE AEROTEKBI/BETA2/FINANCE/AGGCUSTDOWNLOAD_V
SUM
     COMPUTE AGGCUSTDOWNLOAD_V.STAFFCONTRACTOR;
     COMPUTE AGGCUSTDOWNLOAD_V.STAFFCONTRACTORPW;
	 COMPUTE AGGCUSTDOWNLOAD_V.STAFFCONTRACTORPY;
     AGGCUSTDOWNLOAD_V.CUSTOMER.TOTALLOCALSPREADAMT
     COMPUTE AGGCUSTDOWNLOAD_V.AVGHOURSAMT;
     COMPUTE AGGCUSTDOWNLOAD_V.CONTRACTOR;
     COMPUTE AGGCUSTDOWNLOAD_V.ACCOUNTS;
BY  AGGCUSTDOWNLOAD_V.CUSTOMER.BUSUNIT_DISPLAY
WHERE WEEKENDINGDATEFILTER AND BUSINESSUNITFILTER AND DIVISIONORSEGMENTFILTER AND (MANAGEDRESOURCESFILTER OR STAFFINGFILTER OR SERVICESFILTER);
ON TABLE NOTOTAL
ON TABLE HOLD AS REPORT_SUBTOTAL FORMAT ALPHA
END
-*xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
-* Hold File
-*xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
SET ASNAMES = ON
SET HOLDATTR = ON
SET CENT-ZERO = ON
TABLE FILE AEROTEKBI/BETA2/FINANCE/AGGCUSTDOWNLOAD_V
SUM
    AGGCUSTDOWNLOAD_V.CUSTOMER.TOTALLOCALSPREADAMT
     COMPUTE AGGCUSTDOWNLOAD_V.DIFFSPREADPW;
     COMPUTE AGGCUSTDOWNLOAD_V.DIFFSPREADPY;
     COMPUTE AGGCUSTDOWNLOAD_V.CONTRACTOR;
     COMPUTE AGGCUSTDOWNLOAD_V.CONTRACTORSPW;
     COMPUTE AGGCUSTDOWNLOAD_V.DIFFCONTRACTORPW;
     COMPUTE AGGCUSTDOWNLOAD_V.STAFFCONTRACTOR;
     COMPUTE AGGCUSTDOWNLOAD_V.STAFFCONTRACTORPW;
	 COMPUTE AGGCUSTDOWNLOAD_V.STAFFCONTRACTORPY;
     COMPUTE AGGCUSTDOWNLOAD_V.AVGHOURSAMT;
     COMPUTE AGGCUSTDOWNLOAD_V.AVGPWHOURSAMT;
     COMPUTE AGGCUSTDOWNLOAD_V.DIFFAVGHOURSPW;
     COMPUTE AGGCUSTDOWNLOAD_V.ACCOUNTS;
     COMPUTE AGGCUSTDOWNLOAD_V.ACCOUNTSPW;
     COMPUTE AGGCUSTDOWNLOAD_V.DIFFACCOUNTSPW;
BY AGGCUSTDOWNLOAD_V.BUSUNIT_SORT
BY AGGCUSTDOWNLOAD_V.BUSUNIT_DISPLAY
BY AGGCUSTDOWNLOAD_V.CUSTOMER.REGIONNAME_DEFINE
WHERE WEEKENDINGDATEFILTER AND BUSINESSUNITFILTER AND DIVISIONORSEGMENTFILTER AND (MANAGEDRESOURCESFILTER OR STAFFINGFILTER OR SERVICESFILTER);
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE HOLD AS COMPANYAGG FORMAT ALPHA
END
-*xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
-* JOIN
-*xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
-* Join Division subtotals to Details
JOIN
LEFT_OUTER COMPANYAGG.COMPANYA.BUSUNIT_DISPLAY IN COMPANYAGG TAG COMPANYA
TO MULTIPLE REPORT_SUBTOTAL.REPORT_S.BUSUNIT_DISPLAY
IN REPORT_SUBTOTAL TAG RPT_SUBT AS SUBTOTAL
END
-*xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
-* INCLUDE Common DEFINES
-*xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
-INCLUDE IBFS:/WFC/Repository/AerotekBI/Beta2/Common/Fex/DEF_CompanyAgg.fex
-*xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
-* Report
-*xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
SET CENT-ZERO = ON
TABLE FILE COMPANYAGG
SUM
     COMPANYA.COMPANYA.TOTALLOCALSPREADAMT
     COMPANYA.COMPANYA.DIFFSPREADPW
     COMPANYA.COMPANYA.AVGHOURSAMT
     COMPANYA.COMPANYA.DIFFAVGHOURSPW
     COMPANYA.COMPANYA.CONTRACTOR
     COMPANYA.COMPANYA.DIFFCONTRACTORPW
     COMPANYA.COMPANYA.ACCOUNTS
     COMPANYA.COMPANYA.DIFFACCOUNTSPW
BY  COMPANYA.BUSUNIT_SORT NOPRINT
BY  COMPANYA.BUSUNIT_DISPLAY
BY  HIGHEST COMPANYA.TOTALLOCALSPREADAMT NOPRINT
BY  COMPANYA.REGIONNAME_DEFINE AS '&&OFFICETYPE_REGION_TITLE'
ON COMPANYA.BUSUNIT_DISPLAY SUBFOOT
" <+0><RPT_SUBT.TOTALLOCALSPREADAMT><+0><RPT_SUBT.AVGHOURSAMT><+0><RPT_SUBT.CONTRACTOR><+0><RPT_SUBT.ACCOUNTS>"
ON TABLE SUBHEAD
"Overall<+0> > Business Unit  |  <+0>Drill to <+0>Division"
ON TABLE SUBFOOT
"&&WEEKENDINGDATE_LABEL &&BUSINESSUNIT_LABEL &&OFFERING_LABEL &&DIVISIONORSEGMENT_LABEL"
WHERE ZEROSPREADFILTER;
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT AHTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
TYPE=SUBFOOT,
HEADALIGN=BODY,
JUSTIFY=RIGHT,
STYLE=BOLD,
$

TYPE=SUBFOOT,
OBJECT=TEXT,
ITEM=1,
COLSPAN=2,
$

TYPE=SUBFOOT,
OBJECT=TEXT,
ITEM=3,
JUSTIFY=RIGHT,
COLSPAN=1,
$
ENDSTYLE
END  

Webfocus 8202
Windows 10

quote:

TABLE FILE GGSALES
COUNT
DST.UNITS
BY ST
BY PRODUCT
ON TABLE HOLD AS repdata
END

TABLE FILE GGSALES
COUNT
COMPUTE PRODUCT/A16='|Total';
DST.UNITS
BY ST
ON TABLE HOLD AS repdata
END

TABLE FILE REPDATA
SUM UNITS
BY PRODUCT
ACROSS ST
END

Thanks, dbeagan. That gave me an idea. I was able to make it work by calculating the totals separately and then appending them to the data table with a sort flag so that I could keep the totals at the bottom. Very helpful. Thanks again!
STEVE