Distinct Count not workiing as expected

[dev1212]

Hi,

I have a requirement to count the distinct account numbers that has status as 'open' or 'processing'

So, i created a formula field

@countAcctStatus
================

If Uppercase({Acct.Status})='OPEN' or Uppercase({Acct.Status})='PROCESSING'
Then
{Acct.AcctNumber}


I dropped this formula in detail section then created summary on it as Distinct Count.

But when i am doing this i am not getting the result expected. It is calculating distinct counts but also including accounts that are not in status 'open' or 'processing'.

Please reply...

Thanks

 

[lbass]

First, inserting a distinct count on a conditional formula will result in a count that is off by one (unless all records meet the criteria), since it will cluster all records that don't meet the criteria under the default value. You can correct this my adding an explicit null as the default.

Second, if there can be spaces in the field, you should trim the value. So please try:

If Uppercase(trim({Acct.Status}))='OPEN' or
Uppercase(trim({Acct.Status}))='PROCESSING' Then
{Acct.AcctNumber} else
tonumber({@null})

...where {@null} is a new formula that you open and save without entering anything. If AcctNumber is a string, remove the tonumber().

-LB

 

[dev1212]

Hats off to you ..!!
It worked perfectly..

Thanks a lot lbass