Can WEBFOCUS mimic the Pivot calculation like EXCEL 2013 where a distinct count of multiple text values
Don’t know how to better ask this question … Here is an example

In excel I can pivot and there is an option to “add this data to a data model”
This now gives me the option of getting a distinct count on more than one column


Row Labels Distinct Count of APPLICATIONCODE Distinct Count of TEAM_NAME
Human Resources 4 8
Grand Total 4 8




example of the raw data
4 distinct apps with 8 distinct teams but note I have 9 rows … Not withstanding this is correct I have 4 distinct App codes and only 8 distinct team names

[$Range].[Line Of Business] [$Range].[APPLICATIONCODE] [$Range].[APP_NAME] [$Range].[Level2] [$Range].[LAYER_2_MGR] [$Range].[LAYER_3_MGR] [$Range].[TEAM_NAME]
Human Resources REK0 HR Tactical Application Suite HR Business Partner - CM and I&TS Pagano, Helena Melo, Lisa Human Resources - Ireland
Human Resources REK0 HR Tactical Application Suite HR Business Partner - CM and I&TS Pagano, Helena Melo, Lisa Human Resources - Luxembourg
Human Resources REK0 HR Tactical Application Suite HR Business Partner - CM and I&TS Pagano, Helena Melo, Lisa Human Resources - France
Human Resources REK0 HR Tactical Application Suite HR Business Partner - CM and I&TS Pagano, Helena Melo, Lisa Human Resources - Italy
Human Resources REK0 HR Tactical Application Suite HR Business Partner - CM and I&TS Pagano, Helena Melo, Lisa Human Resources - Belgium
Human Resources REK0 HR Tactical Application Suite HR Business Partner - CM and I&TS Pagano, Helena Melo, Lisa Human Resources - Zurich
Human Resources VNC0 WMBI - Prodiance ESM HR Business Partner - Wealth Management and Insurance Colangelo, Connie Lund, Campbell HR WM International
Human Resources XKY0 WMBI - SFPWS HR Business Partner - Wealth Management and Insurance Colangelo, Connie Lund, Campbell HR WM International
Human Resources YEQ0 POLICY & PROCEDURE HR Human Resources Scott, Per Monti, Teri Employee Relations P&CB

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

UPDATE Can I use WITHIN on an EXL2K PIVOT ?

I have found that the key word Within almost works , I do not have a pivot table but I do get the correct distinct count as a summary
Can I use WITHIN on an EXL2K PIVOT ?
The example in the documentation is
TABLE FILE SALES
SUM UNIT_SOLD AS 'UNITS'
AND PCT.UNIT_SOLD AS 'PCT,SOLD,WITHIN,TABLE'
AND PCT.UNIT_SOLD WITHIN STORE_CODE AS 'PCT,SOLD,WITHIN,STORE'
BY STORE_CODE SKIP-LINE BY PROD_CODE
END


My source code is

TABLE FILE HOLD
SUM
CNT.DST.HOLD.HOLD.APP_NAME WITHIN Level3 AS 'COUNT,DISTINCT APP_NAME At Level 1'
CNT.DST.HOLD.HOLD.APP_NAME WITHIN LAYER_2_MGR AS 'COUNT,DISTINCT APP_NAME At Layer 2'
CNT.DST.HOLD.HOLD.TEAM_NAME WITHIN LAYER_2_MGR AS 'COUNT,DISTINCT teams effected at Layer 2’
BY LOWEST HOLD.HOLD.Level3
BY LOWEST HOLD.HOLD.LAYER_2_MGR

ON HOLD.HOLD.Level3 SUBTOTAL AS '*TOTAL'
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT EXL2K

Unfortunately this is not working as expected seams I just need to get the query to run and then complete the presentation of aggregation in EXCEL

Roy

I suggest opening a case to see if there is a way to accomplish this.

Regards