Count distinct?

[#1]

I want to count all occurrences that have a date in my [unique date field] column, but if there is more than one entry the occurs on the same date , only count those that have a distinct [Claim Number].

So with the below example data, the answer should be 6.

7 rows have dates, but there are only 6 with unique claim numbers.

This is the expression I started with:  count([Unique Date Field])

But this expression returns the value of 7. 

What do I need to modify in my expression to have it count as intended?

Example data:

Unique Date Field	Claim Number
5/1/2017	69
5/1/2017	69
5/1/2017	17
5/3/2017	54
5/3/2017	40
5/3/2017	99
5/3/2017	77

[Gysbert_Wassenaar]

Add the DISTINCT qualifier and count the values of the Claim Number field: count( DISTINCT [Claim Number])

[Anonymous]

I understood the question a bit differently - " if there is more than one entry the occurs on the same date " tells me that if the same 69 occurs on the 5/3/2017, it should be counted as a separate count.  If I'm right (?), the expression will be:

sum(aggr(count(distinct [Claim Number]), [Unique Date Field]))

[#4]

No success yet.

Gysbert, When I replace my expression with count( DISTINCT [Claim Number]), the result provides null responses rather than a count.  Did you mean that I should be adding your statement to my existing expression of count([Unique Date Field])?  If yes, how should the combined expression statement read?

Michael, When I replace my expression with sum(aggr(count(distinct [Claim Number]), [Date of EQR])) , the result is a count of 8, rather than the 7 expected.  I'm not sure why.  There are only 6 distinct claim numbers that have a [Unique Date Field] entry in my data.  There are many claim numbers in my data, but only 7 have content in the [Unique Date Field], so that's what I'm trying to have my chart rule out the duplicates in that count.

Any other ideas?

[spividori]

Hi.

In the load script add a field as follows:

AutoNumber ("Unique Date Field" & '-' & "Claim Number") as NroCount

And then: count (distinct NroCount).

See the attached example.

Regards.

[Anonymous]

I can't guess where 8 and 7 are coming from...  With your example, both Gysbert's and mine versions return 6.  See attached.

The results will be different for these two versions if you have, for example, Claim #69 on 5/3/2017 in addition to your current data.  It will be the same 6 per Gysbert but 7 per my version.

[Gysbert_Wassenaar]

Perhaps the attached example helps.