3 Replies Latest reply: Oct 2, 2013 7:35 AM by Jim Gay

# 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!

• ###### Re: Distinct Count Total in Straight Table

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

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

• ###### Re: Distinct Count Total in Straight Table

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.

• ###### Re: Distinct Count Total in Straight Table

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?