Jump to content
Salesforce and other SMB Solutions are coming soon. ×

help how to find percentage in summary field


fmhead
 Share

Recommended Posts

Hello,

 

i was wondering if someone can help me with this. i am trying to get percentage of a field in sub-summary report that i have. Basically i have this.

 

i have a DB that track students, each student has the status of either, Pending, Applied, Enrolled, NonStart, Inact. I wanted to find the percentage for each of this status. if i have 12 student that have the status applied and 20 of Enrolled, i want to find the % for Enrolled and Applie.

i am running filemaker 6.

 

any help would be appreciate it.

 

thank you

victor

Link to comment
Share on other sites

Define the following fields:

 

"PendingOne" -- calc field, numerical, Case(status="pending", 1)

"AppliedOne" -- calc field, numerical, Case(status="applied", 1)

etc for all values of Status

 

"SumPendingOne" -- summary, total of PendingOne

"SumAppliedOne" -- summary, total of AppliedOne

etc - a summary field for each fo the status calcs created above

 

"PendingOne_Percent" -- calc, numerical, 100 * SumPendingOne/Get(FoundCount)

"AppliedOne_Percent" -- calc, numerical, 100 * SumAppliedOne/Get(FoundCount)

etc, again for each of the summary fields.

 

(You don't actually have to multiply x 100, you can just format the field on the report layout as a percent if you prefer, these formulas literally give you output as percents)

 

Report works on the found set. Percents will be each status within the found set as a percent fo the found set. To do the whole student body just to a Show All Records. To get the percentages within some subgroup like students enrolled in Physics in the first quarter, do the Find and again it will work on the found set.

Link to comment
Share on other sites

thats work. one more question i have. the report i am creating, i set up to show in column format. i have include screen shot of the report to show you what i am trying to do, but the % applied keep repeating the same number. can FM 6.0 do this?

 

Thank you

victor

Link to comment
Share on other sites

It's showing you exactly what it should be showing you. Of your current found set of students, 4.2% of them have the status "Applied". That is true over and over again for each individual record, which means you probably don't want that field to be on the individual record at all. 4.2% has nothing to do with Paula or Veronica, it has to do with the sum of all students who have "Applied" in their Status field, divided by the sum of all students in the entire found set.

 

Did you want something different? The percent of Paula's students who have "Applied" next to Paula's name instead of the percent of students in the current found count who have "Applied"?

Link to comment
Share on other sites

Thank you.

 

so there is no way to show the % in column format the way i want for each funding officer.

 

thanks for you help, i appreciate it.

 

victor

Link to comment
Share on other sites

I didn't say there's no way to do it, it's just that I didn't have any idea that's what you wanted. What I suggested for you won't work for that, but that doesn't mean a different suggestion won't do it.

 

Are those subsummary parts, sorted by Funding Officer? Is that what your screen shot shows? And for the "Paula" line, do you want the #Applied Paula-students as a percent of the total Paula-students, or do you want the #Applied Paula-students as a percent of ALL students (or the current found set of students)?

Link to comment
Share on other sites

correct, the sub-summary is sort by funding officer. yes i want the #Applied Paula-students as a percent of the total Paula-students. Out of 115 student, what is the % of applied.

 

Thank you for all your help, I really appreciate it.

victor

Link to comment
Share on other sites

OK...

 

First off, you have some field that determines which Funding Officer is associated with each MESSAGE record. It may be a text field "Funding Officer", or it might be "Funding Officer ID Code" which relates that record back to a table of Funding Officers, I don't know how you built it. But whatever field it is that marks each MESSAGE record as belonging to Paula or Victoria or whoever, make a selfjoin relationship of the MESSAGES table to itself, joining that field to itself. I am going to refer to that field as "Funding Officers" -- you substitute the field you actually use in your solution.

 

 

The selfjoin relationship will prompt the naming of a new Table Occurrence. Let's call it "SameFundingOfficer".

 

Now, back into Field Definitions for the MESSAGES table:

 

here are your fields. Define these INSTEAD: (some are the same, some are different, there are some new fields added, etc):

 

"One" -- calc field, numerical, = 1

"PendingOne" -- calc field, numerical, Case(status="pending", 1)

"AppliedOne" -- calc field, numerical, Case(status="applied", 1)

etc for all values of Status

 

"SumPendingOne" -- summary, total of PendingOne

"SumAppliedOne" -- summary, total of AppliedOne

etc - a summary field for each of the status calcs created above

 

TtlFundingOfficer -- calc field, numerical, Sum(SameFindingOfficer::One)

 

PendingPercentofOfficer -- calc field, numerical, 100 * GetSummary(SumPendingOne, Funding Officers) / TtlFundingOfficer

 

AppliedPercentofOfficer -- calc field, numerical, 100 * GetSummary(SumAppliedOne, Funding Officers) / TtlFundingOfficer

etc -- a xxxPercentofOfficer calc field for each of the status variables

 

 

See attached. Mine is ugly. Make yours pretty.

Link to comment
Share on other sites

Great. it work like a charm.. i notice you mention table which FM 6 does have. i did the relationship base on Fundingofficers and it work. I really appreciate all your help.

 

also some strange reason i cannot see that image you posted. i guest you have to be a paid memeber to see it.

 

thanks.

victor

Link to comment
Share on other sites

I keep forgetting to word things in the language applicable to FileMaker 6 (sorry about that) but glad you could parse the instrux anyway, and got it to work.

Link to comment
Share on other sites

 Share



×
×
  • Create New...

Important Information

Terms of Use