Record Count Help Needed. 1

[BHaines]

I need a means of counting only the number of records which do not have a null in a specific field. ie, there needs to be a value of 1-5 in Field X for the record to be counted. I then need the total number of these non-null records. Any idea how to accomplish this?

 

[ShortyA]

Hi,
you could try creating a formula as per below and then using the formula in the your summaries.

WhilePrintingRecords;
if isnull({Table.Field}) then 0 else 1;

or

WhilePrintingRecords;
if {Table.Field} in 1 to 5 then 1 else 0;

When you use SUM on this formula in your summaries only records that have either a non-null value in the first instance or have a value between 1 and 5 will be counted.

Hope this helps.

 

[BHaines]

Ok, will this work when I'm trying to chart it? I need to average the total scores in a field. Right now it's averaging the nulls in as well.

 

[BHaines]

Ok, I guess I'm confused, because I can't do a sum on that formula. Crystal wont let me. (I'm in version 8.5)

 

[lbass]

Remove the "whileprintingrecords".

-LB

 

[BHaines]

Ok, removing the "while printing records" allowed me to do the sum stuff, but now my charts are going berserk. What I've got to chart these scores on a monthly basis, which I had working, except the values were skewed by the nulls prior. So Now I've writted a couple formulas, one called @FindNulls is used to give the fields a 0 or 1 value:

if isnull({Table.Field}) then 0 else 1;

to get it to only count non-null records

Then another called @SumRecords is used to count the non-null records to get the real record count:

Sum ({@FindNulls})

Then a third, @SumValues which is used to sum the values in the fields:

Sum ({Table.Field})

And finally a fourth, @AverageScores, which averages the mess:

{@SumValues}/{@SumRecords}

Now, when I put these fields into the footer to check the numbers, they check out ok. However, when I then try to chart it monthly, (On change of {Table.datefield} Ordered to be charted monthly showing values for @AverageScores ) all the months are coming out to the same number.

 

[BHaines]

*Blechs at the billion typos.* Should remember to preview before I submit.

 

[ShortyA]

I think the figures will be coming out the same in the chart as the formulas are not at record level (as the first field is).

I had a similar issue with nulls impacting the average values. Have you checked your File/Report Options ? There is a setting for changing NULLs to the default value. In this instance I think you should not have that ticked.

 

[Madawc]

There are easier methods. If you right-click on a field and select [Insert] and [Summary], you should get the option to count. Nulls will not be counted.

If you just want values 1 to 2, do the same but choose a running total.

You could also use a running total to count the number of cases where the field is null. Do a count on some other field that will never be null. But choose [Use A Formula] for [Evaluate] and add only when isnull {that.field}.

Madawc Williams (East Anglia, UK)

 

[BHaines]

ShortyA: That option is unchecked.

Madawc: How will that make charting it work? The big issue here is that the person requesting the report needs it charted in monthly increments for a rolling 12 months for each of 3 main survey responses. I've managed to set the report parameters to only pull the last full 12 months of data and I've been able to get it to average the score field monthly for each separate question, but that average divides by the total number or records.

I considered not pulling records with a null value, but there are actually 3 fields which need to be charted, and as there was no requirement to fill in answers to all 3 questions (This is an online survey), most of the records have nulls in one of the values.

At this point I'm beside myself.

 

[Madawc]

A running total could select for the last 12 months, or for some more complex criterion. Use a formula to evaluate.

Madawc Williams (East Anglia, UK)

 

[BHaines]

I'm very much a beginner, I have no idea how to do what you've just said.

 

[Madawc]

Make a running total to do one of the functions, and then refine it. When you have something that partly works, maybe someone can tell you how to take it further.

Madawc Williams (East Anglia, UK)

 

[BHaines]

Ok, the problem with this is that the field being displayed in the report can't be totalled. It's a text field. I have a function which is stripping the text out of it and converting it to a number. So how do I do a running total based on that?