[CLOSED] Can WEBFOCUS mimic the Pivot calculation like EXCEL 2013
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&CBThis message has been edited. Last edited by: FP Mod Chuck,
WebFOCUS 7.6 Windows, All Outputs
March 22, 2017, 12:21 PM
roy
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
WebFOCUS 7.6 Windows, All Outputs
March 23, 2017, 06:34 AM
roy
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
WebFOCUS 7.6 Windows, All Outputs
March 27, 2017, 11:18 AM
FP Mod Chuck
Roy
I suggest opening a case to see if there is a way to accomplish this.
Regards
Thank you for using Focal Point!
Chuck Wolff - Focal Point Moderator WebFOCUS 7x and 8x, Windows, Linux All output Formats