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: 
phcaptjim
Creator
Creator

Distinct Count Total in Straight Table

I have a question about getting the distinct count of a column in a straight table.  I'm getting closer to figuring this out but cannot seem to arrive at the right value for the total.  I want to get the distinct count where the ATTEST_FLAG = 1 and summarize this count in the # ATTESTING column. Here is what my table looks like:

PROV_IDDATEATTEST_FLAG# ATTESTING
A1/1/20131
A2/1/20131
B1/1/20131
B2/1/20130
C1/1/20130
C2/1/20130

Here is what my current expression looks like in the # ATTESTING column:

=If(Column(28),Count(Distinct Aggr(PROV_ID,PROV_ID)))

I have many columns in my table, but these are the only relevant ones.  Perhaps the sort order is playing a factor here?  Basically I have around 25 columns that all work together to see if the ATTEST_FLAG should be 1 or 0.  Once that flag is set I want to summarize the PROV_ID with a distinct count and get the total. The expression above returns a value 185, but the correct answer is 220.  This seems like a relatively simple expression but I can't seem to get this working.

Any help would be great!

3 Replies
tresesco
MVP
MVP

Check if the following expression is giving you the right count in a text box:

=Count({<ATTEST_FLAG={'1'}>}Distinct PROV_ID)

phcaptjim
Creator
Creator
Author

Yes.  That expression does work in a text box.  I'm using this table to perform some what if analysis on the columns that add up to the ATTEST_FLAG, so before any analysis pieces are selected my total number in the chart should match the number I get in the text box.

phcaptjim
Creator
Creator
Author

Here is something odd, if I select a specific date or date range I get the correct total value.  If no date range is selected, I'm only getting a count for the most recent date.  Do I need my DATE field in my query for this even thought I want to do a distinct count regardless of the date?