Counting summarised fields that match a criteria

[JoeHardstaff]

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

 

[comment]

1 hour ago, JoeHardstaff said:

A calculation is used to create the Above, Below or Expected progress indicator based on the progress points. 

What exactly is this calculation?

 

1 hour ago, JoeHardstaff said:

What I really want to do is count the amount of pupils that are showing as 'Above', 'Expected' or 'Below' where they should be.

Do you mean you want a separate breakout of pupils for each subject?

 

1 hour ago, JoeHardstaff said:

This data needs to be per subject or per pupil.

So that would mean a separate breakout of subjects per pupil?

[JoeHardstaff]

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

[JoeHardstaff]

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. 

[JoeHardstaff]

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. 

[JoeHardstaff]

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

[bruceR]

Quote

I just cant get the SQL to work.

What I want to do is so simple but seems impossible in filemaker. 

Please can someone help here. 

Consider the question of how it would be possible for anybody to help you.

For instance, the correct way to deal with the SQL question is to post your exact query; or post your file; or post a simplified example file.

It is simply a waste of everybody's time to say, you tried SQL, it didn't work.

You have been asked some questions by Comment, just about the best in the biz here. And you have not answered them. Why?

What you want to do probably CAN be done in FileMaker. But you need to do the work of making it easy for people to answer your technical questions by providing enough detailed explanation; together with your actual files or with representative example files.

Also, see: Anatomy of a Good Topic

Edited July 28, 2016 by BruceR

[JoeHardstaff]

45 minutes ago, BruceR said:

You have been asked some questions by Comment, just about the best in the biz here. And you have not answered them. Why?

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. 

[JoeHardstaff]

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