# Counting summarised fields that match a criteria

Discussion created by joe@bridgehouseindependentschool.co.uk on Jul 28, 2016
Latest reply on Jul 28, 2016 by joe@bridgehouseindependentschool.co.uk

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.

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.