Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
CathyRDuvall
Contributor III
Contributor III

Qlik Sense Pivot Table - Count Distinct question

I am working on the Pivot table in Qlik Sense which have both AppIDs and CustIDs but some of the entries do not have a CustID.  I wish to count AppIDs values only one time in each measure of the Pivot Table.  Most of the time this is working but on several entries where the CustID field in blank, that AppID value (even though it is not a duplicate) is not being counted.

I thought using COUNT(DISTINCT AppID) should work but it is not counting the AppIDs that don't have a value in the CustID field.

What am I doing wrong?  Please help.

1 Solution

Accepted Solutions
CathyRDuvall
Contributor III
Contributor III
Author

Tim,

Thank you so much for your help.

I have figured out what I did wrong.  In order to avoid NULL state codes, I eliminated them in that field.  Changed it to no longer eliminate the NULL values in the State field and it brought back in the missing values.

It was your mention of another field that might be effecting the totals that made it investigate.

View solution in original post

9 Replies
sunny_talwar

Would you be able to share an image to show what you are getting and what is exactly you are looking to get?

CathyRDuvall
Contributor III
Contributor III
Author

The highlighted totals below are not correct when compared to counting distinct AppID within the downloaded excel detail document of the same information.  After research, I noticed that if the values under CustID are blank then the AppID is not being counted even though they are not a duplicate.

Capture.JPG

timpoismans
Specialist
Specialist

Could you possible give a screenshot of the entire table, a bit more info on how the data is structured? It's very difficult to figure out what's going on when we just see a few numbers "that aren't correct".

CathyRDuvall
Contributor III
Contributor III
Author

Capture.JPG

timpoismans
Specialist
Specialist

Measures being used are: Count(Distinct AppID) and Count(Distinct CustID)?

Are you certain that when a CustID is missing, there aren't any other fields missing as well that could possible cause the issue?

CathyRDuvall
Contributor III
Contributor III
Author

After removing duplicate ApplicationIDs from the excel document created from the data available, you get a total of 13100.  (see below)

In the 2nd pic below, you will see that 3332 rows do not include the CustomerID.  13100 - 3332 = 9768 which is the total number of Web Views you get in the Pivot table above.

Excel clip 13100.JPGExcel clip 3332.JPG

timpoismans
Specialist
Specialist

Are you filtering on the category Webviews in the Excel as well?

In the first picture, your AppID seems to be sorted low to high, but it's missing all the numbers that you can see in your second picture. Are you certain those AppID in the second picture are supposed to belong to the category Webviews?

If it's possible, sharing example data would be helpful.

CathyRDuvall
Contributor III
Contributor III
Author

Tim,

Thank you so much for your help.

I have figured out what I did wrong.  In order to avoid NULL state codes, I eliminated them in that field.  Changed it to no longer eliminate the NULL values in the State field and it brought back in the missing values.

It was your mention of another field that might be effecting the totals that made it investigate.

timpoismans
Specialist
Specialist

Glad I could help!