All

I'm having an issue with doing a distinct count.

I have a field that represents an account number and it has letters and numbers, so it's a string. I have the field grouped and anytime the account number appears more than once, I want it to be counted once.

example of data

group

3/29/16    lisdexamfetamine    Z921936
3/22/16    guanFACINE ER       Z921936

I want to count that account number 1 per group


I created a variable that's a number that will return a 1 if there's an account number or a zero if not but that won't let me summarize either

3/29/16    lisdexamfetamine    Z921936   1
3/22/16    guanFACINE ER       Z921936   1
4/1/17     texctrune                     0

not sure what else to try...any help?


Edited by mkhenneman - 11 Apr 2017 at 5:28am

I do believe that's the problem. Do you have an example how you would create this work around? thank you.

you can also consider a formula that concatenates the group field value with the account number and do a distinct count of that (at both group and report levels).