Distinct count of a formala field

I have a report that lists visits by patients to clinics,

In the report is a formula field that is looking for patient visits at a specific clinic.  The code is :

if {sp_GetLucentisResults;1.kli_kliniknr} = {?ClinicNumber} then {@EyeID}
else {@null}

When the kli_kliniknr is the clinic we are interested in I display the patients EyeID, otherwise i return null - which is an empty formula.

This works ok.  I get the correct number of visits for the clinic displayed in the report.

However when i do a DISTINCT COUNT of the formula I always get one more visit than there actually is.  I guess this is because "null" is also included in the distinct count.

I could take one from the total, but i may one day have the case that all the visits in the report are at the clinic, so there will not be a null value.  Therefore I will be showing one less visit than there is.

Does anyone have a solution?  How to do a distinct count of a field when sometimes the field maybe empty (null), and should
soozhAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
peter57rConnect With a Mentor Commented:
i think you will have to set a variable if you find a null and then deduct it from the total.

If you arer totalling for the report then in the report header ..

numbervar FoundNull:=0;
""

in your formula field...

numbervar FoundNull;

if {sp_GetLucentisResults;1.kli_kliniknr} = {?ClinicNumber} then
 {@EyeID}
else
(foundnull:=1;
{@null})

then for your total...

numbervar FoundNull;
DistinctCount ({@eyeid})-foundnull


If you are doing group totals then you would need to put the first formula in the group header and total formula would need amending to set the group field in the Distinctcount function.
0
 
MarioAlcaideCommented:
Hi,

You could do something like this

SELECT DISTINCT COUNT(*) FROM YOUR_TABLE
WHERE YOUR_COLUMN IS NOT NULL;
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.