Percent 1

[Kaszalot]

I have a field with the following values (1,2,3 or 4). I want to calculate the percentage of 1's in this field. Do I need a loop for this? Please help. Thank you!

I use CR10 with Access.

 

[lbass]

Create a formula {@1}:

if {table.number} = 1 then {table.number}

Then create a second formula:

sum({@1},{table.groupfield}) % sum({table.number},{table.groupfield})

If you don't have a group, eliminate the {table.groupfield} conditions in the formula.

-LB

 

[Kaszalot]

Thank you for your reply! I do get an error however. When I create the second formula it tells me that the {@1} field name is unknown. These two formulas should be seperate, correct?

 

[lbass]

You need to name the first formula {@1}. Also substitute the correct field names for {table.number} and {table.groupfield}.

If you are still having trouble, copy your formulas into this thread.

-LB

 

[Kaszalot]

{@1} formula:
if {bmoq2.SAT} = 1 then {bmoq2.SAT}

{NAT} formula:
sum({@1},{bmoq2.NAT})%sum({bmoq2.SAT},{bmoq2.NAT})

Yup, still having problems!

 

[lbass]

What is the error message? You have to have a group on {bmoq2.NAT} for this to work.

-LB

 

[Kaszalot]

The group is there.
I get the "This field name is not known

 

[Kaszalot]

Could there be an issue with using a formula within a formula? It seems that it really want a fiekd there and doesn't recognize the formula.

 

[mbarron]

Did you name the formula "@1" or just "1"?

To use it in the second formula, the correct name would be "1"



Mike

 

[lbass]

When you get the error message, is the cursor in front of {@1}? Please verify that your first formula name in the field list (not in the formula) looks like {@1} and not something like {@@1}. Yes, you can use formulas within formulas.

-LB

 

[Kaszalot]

When I get the message, the whole {@1} is highlighted. Should the formula be named "{@1}" or "1"? Maybe it is the naming that's incorrect.

 

[lbass]

When you create the formula, if you enter:

1

it will appear as {@1} on your list. Name it anything you want, but do not add the @ sign or brackets--this occurs automatically. Then choose the formula name from your field list when you create the second formula.

-LB

 

[Kaszalot]

I'm trying to substitute the sum with count, but I'm not getting the same answers. For example the
sum({@1},{bmog2.NAT) should equal count({@1},{bmog2.NAT), shouldn't it? (Since {@1} consists of only 1's).

The sum function returns the sum of all 1's in the set. The count function for some reason returns all of the records in the table.

 

[GJParker]

sum({@1},{bmog2.NAT) should equal count({@1},{bmog2.NAT), shouldn't it? (Since {@1} consists of only 1's).

{@1} will contain 1's only where {bmoq2.SAT} = 1 if {bmoq2.SAT} = 2 or 3 or 4 then {@1} will = 0

i.e.

1
1
0
0
1
1
0
0
1
0

Sum = 5, Count = 10

HTH

Gary Parker
MIS Data Analyst
Manchester, England

 

[Kaszalot]

OK...I know why the count function spits out different result than the sum function; it counts the zeroes as well. Can I compute the percentage of 1's in a filed using the count function?

 

[GJParker]

Instead of using Count try using.

PercentOfSum({@1},{bmog2.NAT)

HTH



Gary Parker
MIS Data Analyst
Manchester, England

 

[lbass]

Sorry, I see that my original percentage formula was wrong. It should have been:

sum({@1},{bmoq2.NAT})%count({bmoq2.SAT},{bmoq2.NAT})

Or, you could use:

sum({@1},{bmoq2.NAT})%count({@1},{bmoq2.NAT})

-LB