JoeHardstaff
-
Posts
19 -
Joined
-
Last visited
JoeHardstaff's Achievements
-
Good afternoon. I have managed to scale my problem down after more hours of researching, reading and experimenting. My table is set up like this with different levels of summaries: Raw data, level 1 summary of pupil (groups the pupil data into how many assessment points they have and displays their progress indicator as above, expected or below) level 2 summary of subject (groups the pupil data into subject areas. Again showing their progress indicator as above, expected or below per subject) level 3 trailing grand summary which calculates the overall progress as a percentage then uses that percentage to display if overall pupils are above, expected or below) The table is sorted by subject, then studentID. This works well. What I have found out is that when I try and do a count of above, or expected etc it is counting every record as if each record has a value that represents the progress. So for example in Art there should be 8 pupils that are summarised as below expected progress, however the count says something like 25. This is because when I have placed the aboveCount or belowCount fields in the raw data as an experiment it is showing a 1 or 0 for every record then adding up these amounts. The calculation is correct but its counting the wrong thing. I only want it to count the amount of pupils that have been summarised as below, above etc. I have uploaded a sample of the layout i'm using to test. AssessmentScores.fmp12
-
I did answer them on the two posts directly below. Sorry I thought that someone could follow the previous posts and see what I was trying to do. I'm not trying to make it difficult for anyone, I appreciate any help I get.
-
OK so I haven't figured this out!!!! What I have done is used the data to calculate the percentage of assessment scores that were above, expected or below, but, that does not calculate how many pupils are above, expected or below in the respective subjects. So I'm still at a loss as to how to do this. I did find an interesting article that explains summary recap, which is what I want to do having looked at the demo file. Here is the link: https://community.filemaker.com/message/215834#215834. I just cant get the SQL to work. It either displays a ? or after taking ages to process the data displays a blank box. What I want to do is so simple but seems impossible in filemaker. Please can someone help here. Thanks
-
I have now figured this out. I missed something really easy. I have made three new calculation fields that shows either a 1 or 0 if the score field is < 1, 1 or >1 then i can use that field to total each category which gives me the individual counts for the amount above, below or expected. I can now use that number to work out percentages etc. Such a simple solution but sometimes you can get wrapped up in different bits when you just need to take a step back. !! Thanks for your time anyway.
-
Here are is calculation used: If ( GetSummary ( Total_assessmentScores; Subject ) > GetSummary ( Possible_assessmentScores; Subject ) ; "Above"; If ( GetSummary ( Total_assessmentScores; Subject ) ≤ GetSummary ( Possible_assessmentScores; Subject ) and GetSummary ( Total_assessmentScores; Subject ) ≥ GetSummary ( Possible_assessmentScores; Subject ) -2 ; "Expected"; If ( GetSummary ( Total_assessmentScores; Subject )< GetSummary ( Possible_assessmentScores; Subject ) -2 ; "Below"; "-" ))) This is used to calculate the progress indicator for individual pupils in this case per subject. When its based on a group of pupils the breakfield is StudentID. Which is another calcuation. When i want to show the progress indicator for a whole subject area or group such as boys, I use a calculation similar to this but it is based on percentage of actual points compared to possible points. This is the calculation: If ( LAC3_PercentProgress_LAC_Overall > 100 ; "Above"; If ( LAC3_PercentProgress_LAC_Overall ≤ 100 and LAC3_PercentProgress_LAC_Overall > 79 ; "Expected"; If ( LAC3_PercentProgress_LAC_Overall < 80 ; "Below"; "-" ))) The LAC3_PercentageProgress is the result of a calculation that simply divides the total points by the possible points and multiplies by 100. So I want to now count the amount of Above, Expected and Below appearances to be able to create percentages in those categories.
-
The calculation uses GetSummary to calculate the amount of actual points compared to the possible points. Then there is a threshold of -2 points. So if the points are above expected it shows Above, if the points are within 2 points of the possible points it shows Expected and if they are below the two point threshold it shows Below. Im not at my computer so can't copy and show the exact calculation. I don't want to group the progress indicators in sub groups of above, expected and below if I don't have to as I want that data on the sub summary by subject. I have many variations of the sub summary, some by pupil, some by subject and some by assessment date. I will have by girls, boys etc as well eventually. Im on my phone at the min bit hope that helps a bit. I'll try and show exact calculations tomorrow. Joe
-
Good afternoon, I am trying to count the amount of sub summaries that meet a certain criteria. I have a table that includes studentID and a progress score for subjects at certain weeks of the year. The data looks something like screenprint 1 (attached). Then I have another layout that shows that data without the body section just the sub summaries, so a summarised view like in the attached screenprint 2. A calculation is used to create the Above, Below or Expected progress indicator based on the progress points. What I really want to do is count the amount of pupils that are showing as 'Above', 'Expected' or 'Below' where they should be. This data needs to be per subject or per pupil. I have sorted all the records so it displays really well per subject, its exactly how I want it. I want to be able to use the count to show a percentage of pupils on, above or below progress. This will later be charted. I have mocked it up in screenprint 3. Any suggestions??? I have tried using a count function, and sql but neither seems to work. Thanks in advance Joe
-
Line graph with multiple lines of comparison
JoeHardstaff replied to JoeHardstaff's topic in Charting
Hi Comment Me again. I have progressed... I have another charting problem... We have a record for each pupil (we are a small independent school) for each week to record their effort in each class (6 per day). So each record stores Monday lesson 1, Monday lesson 2 etc, Tuesday lesson 1 etc etc. It has a total and average also. This bit is fine, all works. I have made a layout that sorts all records by week commencing and then by score (descending order) with sub summaries that includes the average score for all pupils for that week. Again works really well. The chart I want to produce is a line chart that shows effort score averages over the weeks. I can get this for individual pupils, I can even get this for pupils in a certain category such as male/female but what I really want is to be able to have multiple groups on one line chart so that we can compare. My thinking on this is that I create the instances of the effort score table for each group (male, female, free school meals etc) each one filtered so that the average in the summary field only applies to that group. Then create a line graph that uses a new series based on each instance of average score in the table. BUT, I think I need a table that I can use as the layout source that doesn't use one of the existing instances or else the data is not accurate. So I kind of need a middle table to base the layout on that can then be related to each instance of effort scores to display the data accurately. Gosh I do hope this makes sense. I would really appreciate your help on this as its been a big issue for me for some time. Once I crack this I can then use the same principle for other data we have. I know this is a similar issue to that previously mentioned but this may be a bit more simple to understand??? Thanks Joe -
Read more
Hi Nuos
I've just seen a post by yourself on a topic about charting and you sounded like you really now your stuff! So I thought I'd be direct and ask you for help with a charting problem I have. I hope you don't mind.
Firstly I'm fairly new to filemaker. We have just upgraded to 15, run a server (FMS 15) and FM pro (15).
My problem is this...
We have a record for each pupil (we are a small independent school) for each week to record their effort in each class (6 per day). So each record stores Monday lesson 1, Monday lesson 2 etc, Tuesday lesson 1 etc etc. It has a total and average also. This bit is fine, all works.
I have made a layout that sorts all records by week commencing and then by score (descending order) with sub summaries that includes the average score for all pupils for that week. Again works really well.
The chart I want to produce is a line chart that shows effort score averages over the weeks. I can get this for individual pupils, I can even get for pupils is a certain category such as male/female but what I really want is to be able to have multiple groups on one line chart so that we can compare.
My thinking on this is that I create the instances of the effort score table for each group (male, female, free school meals etc) each one filtered so that the average in the summary field only applies to that group. Then create a line graph that uses a new series based on each instance of average score in the table. BUT, I think I need a table that I can use as the layout source that doesn't use one of the existing instances or else the data is not accurate. So I kind of need a middle table to base the layout on that can then be related to each instance of effort scores to display the data accurately.
Gosh I do hope this makes sense.
I would really appreciate your help on this as its been a big issue for me for some time. Once I crack this I can then use the same principle for other data we have.
Thank you.
Joe
-
Line graph with multiple lines of comparison
JoeHardstaff replied to JoeHardstaff's topic in Charting
Hi Comment I am still stuck on this topic. I have now created the single line graphs per pupil, I even managed to implement drop down menus with subject and pupil name that filters the chart showing selection. Still only one line at a time but works well. I scrapped the idea of using SQL as it still didn't do what I wanted. Now I have another requirement. I am working on a line graph again but this time its for effort score per pupil per class. Again i can get individual pupil lines. I now want to compare groups of pupils. I can get single lines using a summary field for average effort score for boys or girls etc over a period of time, but i'd really like to be able to show the average overall effort score compared lets say boys. i have tried to create another instance of the effort score table filter that and use that as a series which almost works but the dates (x axis) go a bit strange and the actual scores seem to be wrong as i have to specify a different table to that in which the layout is based. I did have another idea of creating a fresh table that has a calculated field for all the comparison groups which populates with the summary field using a script that sets the field based on a filter. Then use the newly created fields to make multiple lines on the line graph... Seems like a long winded way but I think it would work if i could accurately get the data in the fields. What do you think? I really appreciate the help by the way. Learning a lot. Thanks Joe -
Line graph with multiple lines of comparison
JoeHardstaff replied to JoeHardstaff's topic in Charting
I used this page for the idea, seems to work well except the format of the date. http://onebusinessapp.com/diydashboard?aliId=236297707 -
Line graph with multiple lines of comparison
JoeHardstaff replied to JoeHardstaff's topic in Charting
I have decided to use ExecuteSQL instead and make a dynamic line chart which is beginning to take shape. But thanks for the help anyway. -
Line graph with multiple lines of comparison
JoeHardstaff replied to JoeHardstaff's topic in Charting
Hi comment Thank you for your reply. I can do the running total via a summary. How do I get the chart for one pupil. Joe -
Good afternoon, I have come to a standstill in developing an analysis section of my database. What I want to do is very simple in most database or spreadsheet applications however, it just doesn't seem to want to work in Filemaker Pro Advanced 14. All I want to do is chart the accumulative score for pupils over a period of 17 assessments. The assessment scores are -2 through to 3. So if assessment one is 2 and assessment two is 1 the accumulative total so far would be three. On the graph i want it to show Assessment one as 2 and assessment two as 3. I would like a line per pupil with the Y axis showing score and the x axis showing the assessment. The idea is that it shows progress over a year. I have all the data in a table but need to show it in a graph. Any suggestions would be greatly appreciated. I am sure you will need more info so if you don't mind could you ask what you need to know so that I can explain further. Thanks in advance Joe
-
Value List items in a table
JoeHardstaff replied to JoeHardstaff's topic in Script Workspace and Script Triggers
Hi everyone thanks for your help. I have cracked it. I used Aurigo's solution eventually. The reason it didn't work was because I needed to add another instance of the 'Results' table to link it to an existing table. Now works fine. The reason I didn't/can't use the originals is because if a staff member adds a new record to the layout based on the main students table then that will add a new students record to the database which I don't want. So on things like the assessment tables I needed all pupils adding with the tables linked via student ID. So now if a new pupil joins the school I only have to click the add new pupil button to add them to the assessment tables and any other tables that I put in the script. I might adjust it so that when a new pupil is added to the database it then runs a script to add the new pupils to the other tables via the student ID using the method above. That's a project for the next half term I think!. Thanks for the help though.
