calculation help needed - basic

[MarijaM]

I'm trying to figure out how to make a report that will give me percentages on responses to a survey. I tried to browse through the existing topics but everything I saw was more advanced than this.

I made a field with a value list -a checkbox set- with five possible responses. I want to make a report that shows the percentage of respondants for each answer. (People could pick more than one.)

I tried making a calculation field using an IF function If ( FieldName = "postcard" ; 1; 0) which I would then use a count field to add up the number. I'm sure there is an easier way. But this doesn't work anyway, as it only gives me the records where they only selected "postcard" and ignores records with "postcard" and "phone call" or other combinations.

Is the solution to use a different operator? Or do I have to make five separate fields instead of using a value list? Or is there some easier way, like maybe a Count function?

Thank you for any help!

[kiwiora]

I am so much a novice, but since no one else has responded...

If you're only wanting to know if someone's responded not what they responded, how about using an if empty? That way it doesn't matter how many values they've selected

i.e

if(not isempty(FieldName); 1; 0)

[comment]

See here.

[Lee Smith]

hi comment,

You made so many Good Post there, maybe you could point to the post number for us slooooooow Senior citizens. Please.

Lee

[Søren Dyhr]

He seems to refere to this:

http://fmforums.com/forum/attachment.php?attid/4249/

But it needs to be changed into checkboxes as well as the calc called:

cSplitValuesR needs to be changed into:

Substitute ( PatternCount ( Extend ( Category ) ; cCategoriesR ) ; "0" ; "")

The field Value as well as SumValues( looses their meaning! But it utilizes Comments undocumented related summary feature - including it's tricky freshing issues. What remains to be done is getting the percentage of the total, which I trust ...even senior citizens to figure out

--sd

[comment]

Lee,

I refer to the entire thread, because I think it's important that the poster HERE read what I - and others - said THERE in the proper context. I am sorry if it causes you problems - but then you are not the poster.

Søren,

I already did all of that in the thread I referred to (your link points to another file in an earlier thread). And there's no need for related summaries here - it can all be done relying on the found set.

While we are at it, could you please not link to actual files, but rather to the threads/posts? That way I can see the context and then decide if I want to download the actual file.

[Søren Dyhr]

Well, as I see it is Lee merely warning, that a talmudist approach to replying isn't working ...what goes on is that you hit another treads referencing a third, makes you think what the f*** and you end up drawing your own conclusions way before reaching the end of each sub-call back to where you started.

Lets get quotes with references or even better synopsises with directions as where you can expand on the subject, when it comes to it is the whole point that the questioner begins to THINK of his case instead of being entertained by our sometimes infinite wisdom as oracles.

The Oracle of Delphi were originally doped severely so you couldn't make head and tail in what she said, so you jumped to conclusions confirming the excuses for the scheming plan you were at.

--sd

[MarijaM]

Hi everyone, thanks for all the feedback.

Kiwiora, thank you for trying to help me. Unfortunately, I seem to not have explained my question well enough. Almost everyone has checked at least one of the boxes, and I'm not really looking for whether the field is empty. I just want a count of how many people selected each of the possible answers.

The payment method question referenced almost answered my question except you could only pick one of the values, and they seemed more concerned with adding up the amount of money coming in from each payment method. In other words it was too different to be helpful to me.

Unfortunately, this is all way over my head. Unfortunately, the "what the f***" multiple thread references did NOT help me draw my own conclusions.... because those situations were different from mine and anyway I just didn't understand any of it.

Right now, I know I can simply do a "find" to get the number of people who checked each answer, and then use a handheld calculator to find the percentage. So that's what I have done in the interim. However, I know that Filemaker can have a calculation field to do this for me. I just don't know how. Sorry but I find the Help in the software next to useless. I spent three hours reading it and trying to figure it out before I posted my question here.

I know what Soren is saying about oracles and advice and jumping to conclusions, but I think that applies more to life decisions than math... at least in my case. So I hope that someone will be kind enough to give me a very simple answer to my very novice question! Thanks!!!

[Søren Dyhr]

When Comment pointed at the correct template it all made sence, try to download his template and investigate click on the rec part of the text, where he writes:

I already did all of that in the thread I referred

It can't be true that you shouldn't consider empties in establishing percentages? In my huble opinion covers Comments template all you asked for initially - do indeed download it, although it includes repeating fields!!!

This is the template:

http://fmforums.com/forum/attachment.php?attid/5506/

--sd

[LaRetta]

I would prefer reading the threads referenced as I go. Then I'm sure I have to correct context when I end up at the file. I had no problem following the logic of it at all. But then I knew that Comment referenced both to add that additional perspective. You missed it, Soren, because you didn't bother reading the threads again - you just clicked link to link.

Marijak, the file Comment referenced will give you exactly what you've asked for. I suggest you take another look.

Update: Get off the repeating fields rant, Soren. They are powerful and useful for reporting purposes as well as other things. They are not being used for data here ...

LaRetta

Edited January 13, 2006 by Guest

[MarijaM]

I did download the file, I looked at all the field definations/calculations. I just didn't understand them.

[LaRetta]

You don't need to understand those calcs to use them, MarijaK. If you want a solution to your problem, you'll either have to learn them, hire a developer to do it for you, or do what I've done (on occassion) ... just copy/paste them. Cheating gets the job done just as well.

There are only two real fields there after all (and only one is applicable to you - the Category checkbox field). The rest are calcs. So create the identical calcs with the same exact calc names and Options (be sure to create the repeating fields as indicated). Then paste the calc definitions (from Michael's file) and simply change the field name (checkbox field) they reference.

If you want to get lazy (like I do), just paste the calc then exit the field definition. FM will tell you the field can't be found and it will highlight the incorrect field (checkbox) name. Just immediately go to the field popup and double-click the CORRECT field name and FM will replace the highlighted portion with the correct field name. And be sure you put your actual value list name, surrounded by quotes, in place of "Categories."

There is simply no other (or better) way to achieve what you wish. Comment's solution is perfect for your needs. If you get stuck, let us know your specific field name and I'll provide the correct syntax for you to copy/paste.

LaRetta

[Søren Dyhr]

They are powerful and useful for reporting purposes as well as other things. They are not being used for data here

Indeed and I as well use them often for the very purpose, but you and I how to balance between abuse and having it as the right tool for the job at hand. But it's tear and wear that makes us aware of the do and don't ...how often havn't we found newbes painting themselves into a corner with them.

It's not a rant but merely a warning!

--sd

[LaRetta]

Hi Soren,

When I started this business a few years back, I heard constantly about NOT using repeating fields. But nobody took the time to say WHEN or WHY. I was told repeatedly they were a relic and should NEVER be used!! Just because someone is a newbie does not mean they lack intelligence or common sense. Because I was told so often (and read constantly) to ditch repeating fields, I missed their tremendous power!!! I regret having listened.

I wish, if we suggest FOR or AGAINST repeating fields, we preface WHY and WHEN they are valuable. If a newbie read your words above, they would be afraid of repeating fields in general (as I was). And I think that's sad because RF's are such a tremendous tool. Even being a newbie, if I was told why they were bad for data-use but could be used for reporting and calculations, I'd UNDERSTAND!

L

[Søren Dyhr]

if I was told why they were bad for data-use but could be used for reporting and calculations, I'd UNDERSTAND!

Excellent suggestion, however the reporting bit needs to be expanded on - it's so that reporting on a RF is close to imposible, but using it as tool in the generation are they right at it.

It's almost like my mum explaining her friends that I'm living in Copenhagen, where I from my perspective would say I'm living in Rodovre which is an sub urban area totally different populated than the metropolic part of the whole thing. She says so because her perspective veiw is seen from 75 miles away.

So the distance to the topic blurs the distinctions slightly, so reporting needs further treatment in your "Rule" - but dispite that is it good initiative ...next question is when to raise the warning flag and when to encourage further dipping toes in the pools deep end.

--sd

[MarijaM]

Okay, after a lot of confusion and using the wrong sample database, I'm now using the SumbyCategorySimple database and I feel very good about the progress I've made.

Thank you LaRetta for your encouragement!

Now I've just got (hopefully) one problem. In the following calculation, where do I put my field name?

Substitute ( MiddleValues ( ValueListItems ( Get (FileName) ; "Categories" ) ; Get ( CalculationRepetitionNumber ) ; 1 ) ;

¶ ; "" )

My field name is RecruitMethod, and there are five choices in the checkbox value list for that field:

Postcard Invitation,

Invited by a friend,

Website Announcement,

Phone Call,

Other

Thank you so much for your help!!

Edited January 13, 2006 by Guest
confusing typo!

[LaRetta]

This calculation doesn't need your field name. It only needs your value list name, so it would be:

Substitute ( MiddleValues ( ValueListItems ( Get (FileName) ; "[color:red]type your value list name here" ) ; Get ( CalculationRepetitionNumber ) ; 1 ) ;

¶ ; "" )

Be sure what you type as your value list name is EXACT or it won't work.

Glad to hear you're working through it! It's an elegant solution. :wink2:

[MarijaM]

Yay!!!

I'm doing a little dance in my office, watching the charts fill up with numbers instead of blank spaces and zeros!

Its hard to explain how satisfying this is, even if it is "cheating", as LaRetta puts it. Thank you all so much!!