Exclude nulls in calculation for a chart

[szed]

I am using CR10 and an access database created by users so I am forced to code around the table set up.

The users have a questionnaire that they rank each question, either 1, 0, or NA. When the value is NA there is a null in the field in the database. There are 40 different questions that are ranked and they store these as individual fields in the table.
The table is set up as follows:
Name varchar(50)
Date datetime
Survey varchar(50)
101 number
102 number
103 number
104 number
105 number
106....... all the way up to 40 fields.


I need to get the following % to display on a chart by the month:
total# of 1's/total# of 1's & 0's
I need to exclude the null fields because they shouldn't be counted as part of the %.

I've tried running totals but the final formula to calculate the percentage will not appear as a selection to display on the chart.

I then tried formulas, with the report grouped by month & then counted each instance of 1 and each instance of 0 for each of the 40 questions, yes 80 formulas! This works until it runs across a field that has all nulls. Then the final % doesn't display at all.


In the initial counting formula I have tried to code against nulls, per a previous posting using the @null formula, and this still isn't working:
@101-0
if {Surveytable.101} = 0 then 1
else if isnull({Surveytable.101}) then tonumber({@null});
@101-1
if {Surveytable.101} = 1 then 1
else if isnull({Surveytable.101}) then tonumber({@null});

I know there has to be an easier way, just can't come up with it today, any suggestions??

 

[Madawc]

When writing formulas, any test for null values must come first. Without isnull, Crystal will stop processing the formula when it hits a null value; the default assumption being that null means show nothing. I made this mistake several times while I was learning Crystal, because it's not obvious to someone who's used to other programming languages.

I also can't see the need for tonumber({@null}). I'd do it as

if not isnull({Surveytable.101}) 
and {Surveytable.101} = 0 then 1
else 0

It might also be easier to do it with running totals, see FAQ767-6524 if you're not already familiar with them.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[szed]

I cannot set the nulls to 0, then it would count them in the final totals, they have to be excluded.
I've tried the running totals, the final calculation for the percentage that I need does not show up as a selection to display on the chart.

 

[lbass]

The problem with your formulas using {@null} is that the null check must always be first. Should be:

@101-0
if isnull({Surveytable.101}) then
tonumber({@null}) else
if {Surveytable.101} = 0 then
1

@101-1
if isnull({Surveytable.101}) then
tonumber({@null}) else
if {Surveytable.101} = 1 then
1

-LB

 

[szed]

I've tried it with the null first and the sum still comes back blank, which in turn makes the final % blank.
I am trying to figure out if I can set the null to something else without it being counted in the final calculation.
Any other suggestions??

 

[szed]

I just wanted to post that I was able to figure this one out using formulas.
I created the following formula:
@101test
if isnull({Observation.101}) then totext("a")
else if {Observation.101} = 0 then totext("b")
else if {Observation.101} = 1 then totext("c");

Then I used the original formulas I had:
@101-0
if {@101test} = "b" then 1;
@101-1
if {@101test} = "c" then 1;

Then I summed on the two above and used those in my total formulas and the final calculations came out correctly.
I just had to assign the null value something in order to exclude it, hope this helps someone else!!