Jump to content
Sign in to follow this  
Prith

Calculate percentage

Recommended Posts

Prith

Hi,

 

I am new to filemaker pro. I've been working on it for 2 weeks. I am hoping somebody could help me out here.

 

I have an Evaluation table. The table consists of fields - Job Title, Department, Years of Experience etc. In my layout, I want to be able to choose a particular Job Title, say "Professor" and find the percentage of Professor records. And similarly for Department. If I choose "English" Department, I want to get the percentage of English department records.

 

This is my first step. My next step is to choose "Professor" and "English" department and find percentage records with Professors from English department. I hope somebody can help me out here.

 

Thanks,

 

Prith

Share this post


Link to post
Share on other sites
Taff

Hi Prith

 

One way of achieving what you want is to create a self-relationship. To do so go to Define Relationship select New and when prompted to open a file select the file you have open. Lets call this relationship Job_Title and match the Job title in the current file with the job title in the related file.

 

Now the relationship is set up you can create a calculation field using this calculation

 

Count(Job_Title::Job Title) / Status( CurrentRecordCount)

 

The first part of the calculation uses the relationship to count the number of job title that are the same. The second part of the claculation counts the number of records in the file. Set the calculation to number. To display this as a percentage - enter Layout Mode and select this newly created field, open the Format menu and select number. Tick the check box for Use Notation and select Percentage.

 

Now create a second self-relationship on the field Department and repeat the above steps.

 

That covers problem one for the next problem you could concatenate the two field job title and department. To do so create a calculation field and use the following calculation

 

Job Title & Department

 

Now create a third relationship as you have already done using this field. And repeat the previous steps.

 

I hope this is of help unfortunately the instructions are for FM5, so FM7 might not be exactly the same.

 

Taff

Share this post


Link to post
Share on other sites
Prith

Hi Taff,

 

I tried your method. And it worked pretty well. Thanks. The only problem is that I have 10 fields in Evaluation and I need to get percentage/count of records for different combinations of them. so the number of self-joins is enormous.

 

So, is there any other way to do it?

And thanks again.

 

Prith

Share this post


Link to post
Share on other sites
Taff

Hi Prith

 

Sorry about the delay but I could not think of another way of doing it. How about trying this method.

 

What if you created a couple of calculation field using the Case function to enter a code? With a different code for each Job title in one field and in the other a code for various combinations. You could then create a third Db file that contains two relationships to the newly created fields in the job title file. You could use these relationships to count and summaries the codes. Only one record would be needed, as you would only enter the codes for the data that you needed at that time.

 

I’ve not tried this method but in theory it should work unless anyone can spot an error in my thinking.

 

Taff

Share this post


Link to post
Share on other sites
Maarten Witberg

I think Prith needs no more than 10 self joins. A split can be obtained by dividing two count fields e.g.

count(sj_job_title::job_title)/count(sj_dept::dept)

 

 

An alternative is to make use of summary functions. This makes it easier to make more splits.

if you want for instance a split by gender of the professors in the english dept, then you'd have to use

s_count_gender= summaryfield = count of gender
gs_count_gender = calc = GetSummary(s_count_gender;gender)

then sort first by department, then jobtitle, then by gender to obtain the correct results.

 

a possible advantage is that this method requires less cpu time. if the self-join count fields are in the layout, they are re-evaluated every time a record is modified. (this was annoying already in my test file with only 44 records, 18 fields and 6 self joins on an admittedly not very fast 800mhz ibook) A GetSummary function is only evaluated when the records are sorted. This of course is also its drawback.

 

kjoe

smile.gif

Share this post


Link to post
Share on other sites
Ugo DI LUCA

Hi,

 

Another method, which would be acceptable only for a few fields if you weren't using FM7 (due to indexing capabilities), would be to create a Multikey calculation, and use a left Global key for a single SelfJoin.

This for sure if your process is to have separate reports running based on given criterias.

 

cMKey =

JobTitle &"¶"&

Department & "¶"&

YearsExperience& "¶"&

JobTitle &" "& Department& "¶"&

JobTitle & " "& YearsExperience& "¶"&

Department &" " & YearsExperience

 

The left part of your relationship would be a global field where you'd input, say "Professor English", which in this case could match the 4th line of the previous Multikey.

Of course, entering "4" would match the 3rd line (YearsExperience) too.

 

You may chain up to 10 field values with this Multikey calc structure, but you would need to stripped each field to 4 characters and concanate them including a space after each string of 20 characters.

Such stripped values could be unreliable at the end.

 

Right("0000"&Left(JobTitle;2)& Middle(JobTitle;6;1) &Right(JobTitle;1);4)

would reduce your field content length to 4 characters.

 

In the Mkey, the longer chain would show as :

field1_Strip & field2_Strip & field3_Strip & field4_Strip

& " "&

field5_Strip & field6_Strip & field7_Strip & field8_Strip

& " "&

field9_Strip & field10_Strip

 

The left part of your relationship should be either scripted or calculated too, so that each field value entered would be stripped to 4 characters too.

 

The Mkey needing to be indexed, you'd better have this key scripted in a text field at the moment of record creation or update, rather than a calculation.

But even then, this concanation would take some time.

 

 

There is another method, much more involved, but far more efficient on the index part.

Create 3 MKey, and 3 global left keys.

The first MKey would hold what you consider the 4 more used values for this search, while the other 2 would each hold 3 fields concanated.

 

Now, you'd script a process triggering 3 Go To Related Records, each foundset IDs being pasted into 3 global text fields (could be repeating) in a Copy All Records.

The script will then loop into gID2 and parse out from this list each ID not included into gID1, then move to gID3 for this same process.

 

Note that you could control this script so that it loops only when needed, according to the fields you're choosing, as you know in which MKey they are.

 

At the end, your gID field would hold all values matching the concanation you were looking at.

You could use this relation gID1-ID as your SelfJoin for the Count() and % stuff

 

I'm conscient this is more an idea for those Script and Relational Maniacs... :rolleyes:

 

HTH

Share this post


Link to post
Share on other sites
Prith

Hey Taff, kjoe and Ugo,

 

Thank you. As of now, I think I'll do kjoe's suggestion of summary fields as I need to do a lot of splits. Thanks kjoe for the great suggestion. Taff, I tried your suggestion too.

And Ugo, thanks for the suggestion. I am a beginner on filemaker pro. I really need to think a lot, to apply it to my database. I'm sure it'll be handy sometime though.

Thanks again.

 

Prith

Share this post


Link to post
Share on other sites
Prith

I have this strange problem now!!

 

I used Taff's method for one kind of report i.e. make a self join and then do a Count(). It works for all fields but one! The Count for a particular field does not get any value. It remains empty. Is there a limit on how many self joins can be created and a count done using it??

 

I am baffled!

 

Prith

Share this post


Link to post
Share on other sites
Taff

Hi Prith

 

Glad one of my suggestions has proved useful. As for your current problem, have you used this relationship successfully anywhere else in your file? If not it may be that the relationship is not valid. If any of the fields used in the relationship are calculations then make sure that the indexing is turned on, and that the results being stored are on the correct setting.

 

Taff

Share this post


Link to post
Share on other sites
Prith

You are right Taff. The relationship was not "set up" right. I discovered that after I took Ugo's suggestion and tried to use a portal with that relationship.

 

Thanks again.

Prith

Share this post


Link to post
Share on other sites
This thread is quite old. Please start a new thread rather than reviving this one.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  



×
×
  • Create New...

Important Information

Terms of Use