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?
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]))
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.
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.
