Distinct count not giving correct count 1

[VE]

Hi again,

I'm using Crystal 2008 connecting to an Access database.

I'm trying to get a distinct count of this formula:

if ({AVPerformance.invoicedate} in #3/26/2011# to #4/29/2011#
and {AVPerformance.AV} <> "")
then {AVPerformance.AV} else {@null}//empty formula

I tried the Running Total method and it is giving me the same incorrect number that I'm getting from Distinct Count.

I'm also not getting a consistant difference, so I can't just add a -2 or +1 to the total or assume that it's regularly counting something it shouldn't (or is there a pattern I can look for?)

Is there any other thing I could try?

Thank you for your help

VE

 

[lbass]

Try a formula like this:

//{@AV}:
if isnull({AVPerformance.AV}) or
trim({AVPerformance.AV}) = "" then
{@null} else
{AVPerformance.AV}

And change your original formula to:

if {AVPerformance.invoicedate} in #3/26/2011# to #4/29/2011# then
{@AV} else
{@null}

-LB

 

[VE]

Hi,

That's definitely getting somewhere. Out of 5 columns of this 2 were right on and three were short 1 (at least some sort of consistancy...?)

I'm going to further expore the idea that this is something in the data rather than just the formulas not working.

Thank you
VE

 

[lbass]

You could test which rows are being distinct counted by adding a running total to the details section and using a distinctcount on {@AV}, evaluate using a formula:

{AVPerformance.invoicedate} in #3/26/2011# to #4/29/2011#

Reset never.

You should be able to identify any unexpected results this way.

-LB

 

[VE]

Thank you. My data in my access database was combining two different data sources and one was calling an AV by one name and the other was calling it something else. I made a formula to account for that and used it instead of {AVPerformance.AV} and the distinct count was correct all the way across . I've asked them not to do that, but I guess I need to check now and then.