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_ID
DATE
ATTEST_FLAG
# ATTESTING
A
1/1/2013
1
A
2/1/2013
1
B
1/1/2013
1
B
2/1/2013
0
C
1/1/2013
0
C
2/1/2013
0
Here is what my current expression looks like in the # ATTESTING column:
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.
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.
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?
