topic Distinct Count Total in Straight Table in QlikView

Distinct Count Total in Straight Table

phcaptjim — Tue, 01 Oct 2013 03:00:30 GMT

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

Re: Distinct Count Total in Straight Table

tresB — Tue, 01 Oct 2013 04:31:41 GMT

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

phcaptjim — Tue, 01 Oct 2013 10:59:42 GMT

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

phcaptjim — Wed, 02 Oct 2013 11:35:18 GMT

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?