Distinct count counts an extra one

[aushia]

I am using Crystal reports XI hitting a SQL database with one table

I am creating a manual cross table flat file source

need to do a simple count and distinct count by year

formula is

If {SPSS_TEMP.DischYear} = "0708" then ({SPSS_TEMP.Encounter#})

then I do a summary to get the count and then a summary for distinct count... I cannot change the formula to

If {SPSS_TEMP.DischYear} = "0708" then 1 else 0 then do a sum because I need to also do a distinct count...

Hope I was clear enough to get your help..thanks so much

 

[aushia]

Sorry my question is, for some reason the count adds "1" to the count every time am I missing something in my code??

 

[IanWaterman]

Use a running total instead which evaluates

{SPSS_TEMP.DischYear} = "0708"

Just add that to formula button on RT wizard and select Distinct count from summary type.

Ian

 

[mocgp]

Yeah, what Ian said. The reason you are getting an extra count is that your formula doesn't specify a THEN clause. If the DischYear = "0708" then you are getting an Encounter # and those are being counted. But if the DischYear is not equal to "0708" then you are getting a blank (or a null) and that gets counted as a distinct value.

If you placed the DischYear = "0708" in the report selection criteria, then you would only have the values that matched and the count / distinct count would work properly.

 

[aushia]

Thanks Mocgp but I need to do a manual crosstab and once I get this right for 2007 then the next column is 2008..I would do a crosstab through the wizard but this crosstab is more complex the next colume is the number of diagnostic tests, then the next column needs to be a calculation of the number of tests per patient then I need to do the next columns the same as 2007 for 2008 then do a calculation for the % variance ...so if I select on 2007 I can't do the rest of the crosstab...am I fighting a loosing battle...I am now messing around with running totals but that is not something I use too much

 

[lbass]

Instead of running totals, try the following. Create a formula {@null} by opening and closing a new formula without entering anything. Then change your conditional formula to:

If {SPSS_TEMP.DischYear} = "0708" then
{SPSS_TEMP.Encounter#} else
tonumber({@null})

You can then insert a distinctcount on this. If the encounter# is a string, remove the tonumber().

-LB