How can I account for the "dreaded" null when doing a dist count

[eo]

Hi

CE10.

I have about 20 reports which rely on dist counts to show totals. The problem is that using a std dist counts can cause variances either way. I will try and explain:

Risk No Date Col-A
GH1 Dept 1
Det 12344 01/01/2006
Det 12344 02/01/2006
Det 12344 03/01/2006
Det 98761 08/01/2006 98761
Det 98761 01/01/2006
Det 88844 01/01/2006
Det 17774 10/01/2006 17774
Det 17774 03/01/2006

I ultimately need to est what percentage Col_A is of Risk no.
A distinct count of Risk no gives a total of 4 (correct). But Col-A gives a dist count of 3 (incorrect, it counts the null as well).
At first glance a solution would appear to be dist count of Col-A minus 1. But this does not work in all cases. For example in the following dataset this will not work:

Risk No Date Col-A
GH1 Dept 1
Det 98761 08/01/2006 98761

A dist count of Risk no is 1, and of Col-A (minus 1) would be 0 (clearly incorrect).

I have even tried elaborate coding to account for all eventualities, but as you can imagine, there are simply too many.

The only solution would be to do a dist count of only real characters (ignoring nulls). Does anyone have any suggestions?




EO
Hertfordshire, England

 

[Turkbear]

Hi,
Create a formula that tests for NULL in the Col-A field and sum it to use in your summary formulas:
@CountIt
If IsNull(Col-A) then 0 else 1

Place this in the details ( you can supress its display) and insert a summary ( use Sum not Count)..





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[BobSuruncle]

Turkbear:
That would work well if the value in column A is always different (or Null).
But if a value in Coumn A were ever to repeat you'd get the wrong results.
EO doesn't tell us if this is the case, but just to cover that eventuality, we could approach it in a couple of different ways.
1.) Use a Running Total field to do the DistinctCount. This works well unless you need to reference the DistinctCount value on the detail line (e.g. doing % of total)
2.) Use a NULL formula, as follows:
a) Create a string formula (call it NullString)with nothing in it at all.
b) Create a formula to evaluate each record (place it in the details section )
@UniqueColumnA
If IsNull({Table.ColumnA}) then {@NullString} else {Table.ColumnA}
c) Suppress this formula
d) Do a DistinctCount of this formula



Bob Suruncle

 

[lbass]

A distinctcount of a database field will not count null values, so I'm wondering whether Col-A is a formula, and if so, what is the content of the formula?

-LB

 

[eo]

Hi all

Bob - you are correct, column A could definately repeat. This is why a dist count on Col A is required.
LB - Col a is indeed a result of a formula. You will see, col A is simply a replica of Risk no where a certain condition is met, in this case where the Date > 04/01/2006.
The real formula is:

//isolate success firm orders
If ({TRKTABLE.TRACKTYPE} = 'CONCER'
   and {TRKTABLE.CMPLTDTE} < {RISK_VERSIONS.INCDATE})
then {RISK_HEADERS.RISKREF}
It would help if something like ...else isnull was possible, this either forcing the RiskRef, or a null.

I have also tried a variation of TB's formula, allocating a 1 to where the condition was met (in addition to the existing formula), then to Max that field in the GH. If the max = 1, I know I need to subtract 1 from the dist count, but what if there was only one record. My theory failed...

Any ideas guys?

EO
Hertfordshire, England

 

[lbass]

Bob has two solutions that should work. If you use the running total approach, use a distinctcount of {@col_A), evaluate using a formula:

//if riskref is a number, use:

{RISK_HEADERS.RISKREF} <> 0

//if riskref is string, use:

{RISK_HEADERS.RISKREF} <> ""

-LB

 

[IanWaterman]

Instead using a summary use a formula based on Turkbears idea

@DistinctCount

If distinctcount(fieldname) = 1 then 1 else
sum (@countformula)

If this is based on a group then change to

If distinctcount(fieldname, groupfield) = 1 then 1 else
sum (@countformula, groupfield)

Ian

 

[PaulP2]

I just joined this board to research this exact problem, so I am really glad to see a recent thread on this. I seem to have been inspired by what I read here to stumble on a solution (at least it seems to work in my report):

Create a formula @formulafieldnull
if @formulafield = "" then 1 else 0
(this is for a text field; you can do the same for 0 values or null values, I imagine)

Sum this formula for your group level. If there are empty values, the sum will be > 0. If there are no no empty values the sum will be 0.

Create another formula, @formulafieldcount
if Sum ({@formulafieldnull}, {grouplevel}) > 0 then DistinctCount ({@formulafield}, {grouplevel}) - 1 else DistinctCount ({@formulafield}, {grouplevel})

You would have to do this with each group level and the grand total. It subtracts one from the distinct count when there are any empty values, and doesn't subtract when there are empty values.

I have to confess I didn't understand some of the solutions offered, so if this is duplicating someone else's solution, my apologies.

 

[PaulP2]

The last phrase of the second-to-last paragraph should read "and doesn't subtract when thare are no[\b] empty values.

 

[crcdata]

EO, I am using CR 10. I had to deal with the same problem all day today. I know how to exclude nulls with running totals but not summarie fields or crosstab, until now.

Running Totals:
You can use the evaluate condition formula to exclude certain values. It requires a true/false formula.

(Basic) Syntax Example:
If {Table.Field} = "" then Formula = True
If IsNull({Table.Field}) = True then Formula = False


Summary Fields or Cross Tabs:
There is a "software switch" available. Go to File, then report options. The first two check boxes on the dialog are "Convert Database NULL Values to Default" and "Convert Other NULL values to default".

Be sure that both of them are unchecked. After I did that, the distinct counts ran fine in my cross tab.

 

[crcdata]

CRCDATA again.

I forgot to mention that I am using a formula that needed to be set to null.

To do so, I created formula called "NULL Formula". After I created it I left it blank. No statements or anything in it.

Then I modified my first formula with an if then else statement.

If {@My formula} = True then
formula = {Table.Field]
Else
formula = {@NULL Formula}
End If