Counting summarised fields that match a criteria
joe@bridgehouseindependentschool.co.uk Jul 28, 2016 5:54 AMGood 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.
This is the calculation:
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 either subject, AssessmentDate etc. 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.
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.
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. I saw a post about summary recap which seems to be the closest I have been to the solution I am looking for, but I couldn't get the SQL to work.
Thanks in advance
Joe

screenprint 3.png 45.8 K

screenprint 2.png 91.4 K

screenprint 1.png 30.8 K