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.

Thanks in advance

Joe

Hi,

I apologize for not addressing your precise issue, but I want to recommend the following as a way of cleaning

and speeding upyour code (from the firstyou provided above) ...ifLet(

[

totalBySubject = GetSummary ( Total_assessmentScores; Subject );

possBySubject = GetSummary ( Possible_assessmentScores; Subject )

];

If ( totalBySubject > possBySubject;

"Above";

If ( totalBySubject ≤ possBySubject and totalBySubject ≥ possBySubject -2;

"Expected";

If ( totalBySubject < possBySubject -2 ; "Below"; "-" )

)

)

)

This approach might help you (and others) find what you need.

(I didn't check this for perfect syntax.)