Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
=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!
Check if the following expression is giving you the right count in a text box:
=Count({<ATTEST_FLAG={'1'}>}Distinct PROV_ID)
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?