Troubleshooting
Problem
How can you perform a Count Distinct for a column based on another column? Example: You have a column that has Employee ID's in it where there are multiple entries for each employee. You have another column that has an Active status flag and the goal is to see how many employees have an active flag.
Resolving The Problem
Use a calculated column that does a count distinct on the Employee ID's and then build a filter to only bring back the records that have the active flag.
Another alternative is to use the following in a calculated column:
count (distinct if (<active flag column> = 'Y' ) then (<Employee ID column>) else NULL )
[{"Product":{"code":"SSTQPQ","label":"Cognos Series 7"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":"Impromptu","Platform":[{"code":"PF025","label":"Platform Independent"},{"code":"PF033","label":"Windows"}],"Version":"Cognos 8 BI Framework Manager 8.1;Cognos 8 BI Query Studio 8.1;Cognos 8 BI Query Studio 8.2;Cognos ReportNet 1.1MR2;Cognos ReportNet 1.1MR3;Impromptu 5.0","Edition":"","Line of Business":{"code":"","label":""}},{"Product":{"code":"SS9RTN","label":"Cognos 8 Business Intelligence"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":"Framework Manager","Platform":[{"code":"","label":""}],"Version":"","Edition":"","Line of Business":{"code":"","label":""}},{"Product":{"code":"SS9RTN","label":"Cognos 8 Business Intelligence"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":"Query Studio","Platform":[{"code":"","label":""}],"Version":"","Edition":"","Line of Business":{"code":"","label":""}},{"Product":{"code":"SSQJXN","label":"Cognos ReportNet"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":"ReportNet","Platform":[{"code":"","label":""}],"Version":"","Edition":"","Line of Business":{"code":"","label":""}},{"Product":{"code":"SSTQPQ","label":"Cognos Series 7"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":"Impromptu","Platform":[{"code":"","label":""}],"Version":"","Edition":"","Line of Business":{"code":"","label":""}}]
Historical Number
80074
Document Information
Modified date:
15 June 2018
UID
swg21348942