Let's Get Social!
Focal Point    Focal Point Forums    WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Counts in Subtotals
 Go New Search Notify Tools Reply
 [SOLVED] Counts in Subtotals
Member
 posted July 24, 2020 05:21 PM
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,

WebFOCUS 8201
Excel, PDF, HTML

 Posts: 11 | Location: Charlotte, NC | Registered: February 02, 2018 IP
Platinum Member
 posted July 25, 2020 10:15 PM Hide Post
```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```

WF 8.2.06
Win10 / IE11
AHTML EXL2K PDF

 Posts: 180 | Registered: October 19, 2010 IP
Member
 posted July 27, 2020 08:16 AM Hide Post
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
SUM
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
SUM
WHERE WEEKENDINGDATEFILTER AND BUSINESSUNITFILTER AND DIVISIONORSEGMENTFILTER AND (MANAGEDRESOURCESFILTER OR STAFFINGFILTER OR SERVICESFILTER);
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.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  COMPANYA.REGIONNAME_DEFINE AS '&&OFFICETYPE_REGION_TITLE'
ON COMPANYA.BUSUNIT_DISPLAY SUBFOOT
"Overall<+0> > Business Unit  |  <+0>Drill to <+0>Division"
ON TABLE SUBFOOT
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT AHTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
TYPE=SUBFOOT,
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

 Posts: 19 | Registered: February 12, 2019 IP
Member
 posted July 27, 2020 05:43 PM Hide Post
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

WebFOCUS 8201
Excel, PDF, HTML

 Posts: 11 | Location: Charlotte, NC | Registered: February 02, 2018 IP