Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
mcrea22
Contributor II
Contributor II

How to sum a distinct count if for a cross table

I have a data set that looks like this:

Report Date  Unique Key            Responsible Person        Employee

11/10/2021   abcdef11102021    John J                                 Amy A

11/10/2021   cbcdef11102021  John J                                  Charlie C

11/11/2021   tbcdef11102021   Kim K                                   Tim T

11/12/2021   rbcdef11102021  Dan D                                    Rebecca R

The following statement  =IF ([Report Date] = [Max Date], COUNT(DISTINCT([Unique Key]),0)  is working normally to roll up all new instances by Employee, but I cannot seem to figure out how to Sum this count by [Responsible Person] in a cross table as a responsible person may have many subordinate employees. Any suggestions?  

 

Labels (2)
5 Replies
Digvijay_Singh

What would be your expected output for the data set you have shared?

Rockstar7
Partner - Creator
Partner - Creator

@mcrea22  try this

Aggr(Sum(IF ([Report Date] = [Max Date], COUNT(DISTINCT([Unique Key]),0)),[Responsible Person])

mcrea22
Contributor II
Contributor II
Author

No luck. This returns a null value for the responsible person unless I filter the chart for a specific person and date.

Rockstar7
Partner - Creator
Partner - Creator

@mcrea22  Can you please provide sample dataset and expected output 

mcrea22
Contributor II
Contributor II
Author

If MaxDate = 11/10/2021 I  would expect to see the following result in my cross table:

where the employees and their unique keys are nested below their responsible persons 

Report Date      Responsible Person        CountofUniqueKeys

11/10/2021       John J                                   2

11/11/2021       Kim K                                     0

11/12/2021       Dan D                                     0