Averages

[skynight]

New to FP7. Would someone more knowledgeable than I tell me how to make a calculation field that allows me to display an average (as a percentage) of the number of times a "YES" radio button (or check box) has been set compared to the "NO" box? A "Not Applicable" choice or no choice at all should not count.

In case that is possible, what about calculating a percentage based on three or more buttons? ("low" to "Medium" ot "High" for instance).

Thanks in advance...

[CobaltSky]

Hi,

The answer is that it depends, among other things, on:

1) how your database is structured

2) whether the data is stored numerically or as text values

2) whether the yes and no calues are in the same field or in different fields, and

3) how you want the summary data presented (live on screen, previed/printed report etc)

If your answer 'elements' are already stored numerically in separate fields, you could either define a summary field to return the total of the 'yes' values and then reference it in a calc to divide it by the total number of records using the Get(TotalRecordCount) function.

Alternatively you could set up a relationship from a summary table to the table where your data resides and then define a calc in the summary table, along the lines of:

Sum(YourDataTable::YesAnswers) / Sum(YourDataTable::QuestionSerial)

or similar. If the data is stored numerically in a single field (eg zeros for no and ones for yes) you coudl achieve something simlar using: Average(YourDataTable::YesNoAnswers) - again as a calc in a related summary table. In either case, the group of records being summarized will depend on how you have defined the relationship between the summary table and the data table (whereas with a summary field approach within the data table itself, the geoupo of records being summarised will depend onm the found set).

As you can see, there are an abundance of options. Without a lot more information, it is not possible to recommend a 'best' approach for your current situation. But hopefully the above may give you a few ideas to work with.

[-Queue-]

For the denominator, I think Ray intended to use Count(YourDataTable::QuestionSerial). Summing a serial could produce quite surprising results.

[skynight]

The YES and NO (and NA) buttons are in the same field. Stored as text values, but I want them to count as, say, 1 and zero so that the total will give me the percentage. For example if there are 80 answers and 60 are YES the calc will show 75 percent. Or a simple calculation that will show the number of YES divided by the number of NO. I can see how it is done using separate fields, but not how to do it using the same field but separate radio buttons or check boxes.

[-Queue-]

Use two calculation number fields, one equal to Field = "yes" and one equal to Field = "no". Then create a Cartesian self-join relationship (from table::serial X table::serial) and use Sum(selfrel::calcfieldyes) / Count(selfrel::serial) for the 'yes' result and Sum(selfrel::calcfieldno) / Count(selfrel::serial) for the 'no' result.

[CobaltSky]

For the denominator, I think Ray intended to use Count(YourDataTable::QuestionSerial). Summing a serial could produce quite surprising results.

Quite right Queue... Thanks for catching that.

[skynight]

Thanks for the help, but I am still in trouble. When I specify the Average calculation I get a message that the specified table cannot be found. I also do not know how to set up the Yes No buttons to be counted as numbers. I have the book by Jonathan Stars (Learn Filemaker Pro 7), is there a reference to this subject in there, or some tutorial on the web I could use?

I have a field called Compliance and it has radio buttons for Yes, No and NA. I have tried it as a numbers type and as a text. I have another field, called Group Grading and is typed calculation. Using the supplied calculations, I select Average and type in the formula you supplied, ie (Compliance::YesNoAnswers). But when I try to select it (OK) it tells me that the table cannot be found. I know I am missing some step that you assume I should know to do, could you point me the right way?

Actually, what really worries me is that I am starting to enjoy this!

[-Queue-]

See my post above for using number calculation fields on your yes/no field.

Is there a relationship from the current table to Compliance? If not, you'll need to create one. If the fields are contained in the current table, then you'll need a Cartesian join, explained above.

[skynight]

Must be missing something basic. I set up the calculation as you describe, and it tells me unable to find the specified table. I am working in the same table, with one field having check boxes (Yes, No). I have tried defining them as text and as numbers, and I have another field called Average that is a calculation field, and it is in that one that I am trying to set up a calculation to find the average of the two check boxes. All this in the same table. It seems to me that this would be a simple thing for this program, it is certainly easy in Excel or other database programs. The workbook I have does not mention anything about this type of calculation, which is why I am asking for your much-appreciated help here. I tried setting up two fields instead of the checkboxes, one called Yes and the other No, but how do I indicate the choice? I dont want my customers to have to type in the words, and how would the program know that a choice had been made? What if the customer typed an entry into both fields? Is there a way to set it up using scripts? Getting frustrated now... Not a good sign.

[bruceR]

1. You may not want to use a calculation at all. You may want to use a different field type, a summary field. Take a look at that.

2. If you are having "can't find this field" problems then you are doing something very basic wrong and probably none of your calculations will work. Learn to use the field selector on the top left part of the define calculation dialog. Select a field name and double click it to insert and it will automatically be correct.

[comment]

Must be missing something basic.

In Filemaker, "Yes" and "No" are text. They cannot be averaged. What would be the average of 50 "Yes" and 50 "No"? "Undecided"?

Only numbers can be averaged. To solve this, you need to translate the user's choice into numbers.

One way to do this is to add two more fields to your table. These would be calculation fields, with the result set to number. The first field would have this calculation:

ChoiceField = "Yes"

(This is a short way to write "if user selected "Yes", then 1 ; else 0".)

Once you understand this, the rest should be pretty obvious.

Alternatively, you could make the user choose from numbers to begin with. For example. the value list for the radio buttons could be "1

[-Queue-]

Here's a sample file that shows a ways to get the averages for the found set and for the whole table.

SkynightAverage.zip

[skynight]

For Comment: How does the new field know where or what the choice button is? Does it look for all YES choices in the table? Or must I tie them togehter somehow (and there is nothing in the book about links in the same table)?

For Queue: your attachment is like a pirate's map that needs a piece someone else has. I dont have a program to read it (tried Word, FP, Excel, IE etc). I feel it has the answers I need, but it just sits there and grins at me.

[Sxeptomaniac]

skynight: It's a zip file. Windows XP should be able to open it. just save it to your desktop, then double-click it. When the window opens up, there's an option to extract the file on the left. Once you do that, a wizard will give you the rest of the instructions.

[comment]

How does the new field know where or what the choice button is?

By the field name. Since you didn't tell us what name you have given to the field storing the choice (i.e. the field that is formatted as Radio Button Set), we are all making up our own names for it - resulting in further confusion.

I used the name ChoiceField. In his sample Queue calls it YesNo. You can call it whatever you like (within FMP's naming guidelines). Then, when you define a calculation field, you reference other fields by their names (or, if the fields are in another table, by their "full" names, i.e. tablename::fieldname).

Does it look for all YES choices in the table? Or must I tie them togehter somehow

The calculation

ChoiceField = "Yes"

is specific to the record. If in a record, user selected "Yes", the calcfield (let's call it cYes) will return 1. In another record, the same field may return 0. It simply translates the textual choice into a numeric one.

You need to tie ALL of the records together, so that we can look at the totals of cYes.

there is nothing in the book about links in the same table

I suggest you read these 2 topics in FMP Help:

1. Working with related tables and files > About the types of relationships > About self-joining relationships

2. Working with related tables and files > About the types of relationships > About relationships using comparative operators (see the "x" relational operator - this is called a Cartesian relationship).

---

Queue's file is .zip archive containing a .fp7 file.

[comment]

Here is another sample file - it shows 4 different ways to summarize the results.

Note that the 4th method needs no relationships and uses only 2 fields: one is the user's choice, and the other is the calculation of the result.

Survey.zip

[skynight]

I appreciate the help you are offering, but so far no dice. I know about zipped files, the problem is that I only have FP7 and it cannot read the file you sent. I looked up converting from earlier versions of filemaker and that is way beyond my skill level. I also have Microsoft Office but none of the programs there could open the file either.

I looked up cartesian relationships in the FP Help files but it does not recognise the term.

I have seen you use the : connector, and think it means that I can specify a connection betoween two fields that way. Can I also use it to define the radio buttons or check boxes in a field or formula? (if I want to refer to COMPLIANCE field and check box NO would I write Compliance::No?) Is the formula case sensitive? (if the field is named COMPLIANCE can I use compliance?).

I have read and re-read all that has been offered here, and I appreciate it.

It still seems a simple requirement, a pity the answer is not, since I have a further request if I can get this part to work: I want two fields to display the percentage of YES to NO answers for not only the entire Table, but also for each section of the Table (I have each page numbered and would like the sumary to reflect the result of the YES/NO calculation for the current defined page list as well as the overall result for the entire Table). Bit tougher?

Merry Christmas to all...

[comment]

I know about zipped files, the problem is that I only have FP7 and it cannot read the file you sent.

Do you mean that you have extracted the file from the zip archive, and Filemaker will not open it? If so, the problem seems to be on your side. I had no problems opening Queue's file, and I presume others succeeded in opening mine.

Why don't you post your own file, so we can speak in your own terms, and guide you according to that?

I looked up cartesian relationships in the FP Help files but it does not recognise the term.

No it does not. "Cartesian" is a general term. That's why I referred you specifically to the section that deals with Cartesian relationship - without using this term.

It still seems a simple requirement, a pity the answer is not

My file - if you ever manage to open it - will show you 4 different ways to satisfy your requirement. If you call this "the answer is not", I wonder what would constitute a "yes".

[skynight]

Thanks for the effort you guys are putting in to help me, I appreciate it. By the way, Merry Christmas.

When I download the files, both of them sent so far, I get an initial message telling me that Windows does not recognise them. I see that they are FP7 files, but my version, which is FP7 also, cannot open it. I assume it is an example of a data base with the calculations in it, and it would be wonderful to get access to it, but something is preventing me. I have tried to open the file using Word, Excel, Access and of course FP7, but all I see is random symbols.

I have not decided yet what I want the program to do with my data, apart from initially having it summarise the answers the respondents give me through the choice of radio buttons or check boxes, so I want to learn how to use the calculations section of the program and to see its possibilities. I particularly need to see how to define the response to clicking on one of those buttons, how to tell the program to recognise that this has been done, and to count the number of times it has been done so it can compare the different choices and display the result. I really dont know how to express this any clearer.

Dont give up on me, please!

[comment]

1. Do you mean that you have extracted the file from the zip archive, and Filemaker will not open it?

2. Why don't you post your own file?

[skynight]

Playing with the checkboxes. Can I set it so that only one of the boxes can be selected? If three boxes, Yes, No and NA, if the respondent clicks Yes then the other two boxes are blocked?

Still trying to get some values for these boxes. Can I tell the box to record a value when selected? Using script? A calculated value? (put each on a separate line or use the : symbol to separate them?).

Using value list to give the buttons a numeric value sounds fine, but how do I then display the text I want? (YES, NO, NA). How to get FP to give me a percentage output of these buttons and how do I know the calculation will apply to the whole file? Can I make it look at only some pages in the file, so that I can display an average result for, say, the month of June?

The field is called COMPLIANCE and there are the three radio buttons or check boxes Yes, No and Not Applicable. I want the system to only allow one box to be checked, and for the NA button to have no value, so that if it is checked the result will not be affected.

I saw in the book by Stars that he uses the symbol :: but does not define it. Does it link two fields or part of a field? (say, Compliance::Yes will indicate the Yes button having been selected in the Compliance field?

Thanks for your help..

[skynight]

1. Yes, I have extracted the file, I tried saving it and getting to it from there with the various programs I have to read files (FP, Excel, etc) but cannot open it. Windows will only open it as a text file, which is scrambled.

2. I have posted the file, you will see a couple of fields that I was playing with, trying to get the info I need. I want the Group Grading to return the percentage of Yes selections for Compliance for the group (about twenty pages per group) compared with the number of Yes and No selections. NA selections and no selection should not count. The Audit Grading would be for the entire file, all pages. In other words, if respondents select 50 Yes buttons and 10 No buttons the grading should be 83 percent.

Looking forward to your help...

fileville2.zip

[comment]

Let's see if you can open your own file.

A couple of notes:

1. Checkboxes allow multiple choice; for a single choice yu need to use radio buttons.

2. I assume by "pages" you mean "records". You should always make sure your records are auto-numbered.

3. The requirement to average Group and Total separately, when added to the requirement to ignore N/A choices, complicates things slightly. Perhaps a better solution would be to construct a report with subsummaries.

IAPTemplate.zip

[comment]

Sorry, stupid mistake:

Please change the definition of cComplianceAsNumber to the following:

Case(

Compliance = "YES" ; 1 ;

Compliance = "NO" ; 0 ;

"N/A")

[comment]

And here is a much more simple version.

IAPTemplateS.zip

[skynight]

Had to use my wife's computer to download my own file and then email to me, where it works. I will try the same for the other files. must be something wrong with the program on this computer, which is my company's laptop. Will get the IT guys to fix it.

All I have seen so far is my own file, after you have worked on it, and my response is Case("WOW!;thanksalot).

I will play with it and see if I can learn, but it looks so much more advanced than what I know about FP I might not be able to.

Hope you did not spend quality time on this...

[comment]

Don't worry about it - the time was well spent learning something new, as you can see here.

The first file is actually much more complex than it needs to be, due to a misunderstanding between me and FMP regarding data types. Let me know if the second, simple, version is not up to the task, and I will streamline the first version.

[skynight]

I am looking at the simple template you gave me, and it looks about right for the purpose. I am trying some data in it and have some questions if you could help:

I wanted the user to only have to insert the information in the header section one time (Type of audit, name, date etc). And for this info to repeat and show on all pages of the subsequent data input, all records as it is filled out. Is there a way to do this?

Looking at the calculations, can you tell me what cComplianceasnumber means? Why the little c, and is the field not displayed? Does FP work with a field even if it is not displayed? In other words I could put a lot of the stuff I need in the Define Database table and not set it into the user page at all?

What is the significance (and need) for Unstored and Global options? Would the calculations and data collection work without these settings?

In some of the Sample templates they show fields with hash marks (<< >>) around them. What does this do?

[comment]

I wanted the user to only have to insert the information in the header section one time

Use global fields, then.

Read about global fields in FMP help:

Creating a database > Setting options for fields > Defining global fields (fields with global storage).

cComplianceAsNumber is a name I gave to a field. It doesn't have to mean to anything. I could have called xGfl#r, and it would work the same. As it happens, the c signifies a calculation field, and the rest is pretty descriptive, don't you think? The calculation gets the field Compliance as a number, instead of the original text.

I could put a lot of the stuff I need in the Define Database table and not set it into the user page at all?

Correct. The user needs to see only what he/she needs to see. Make another layout for yourself only, so can you can track the behind the scenes action.

Regarding << this >>, see FMP help:

Creating and managing layouts and reports > Adding fields to a layout > Placing merge fields on a layout