4 Replies Latest reply on Jul 28, 2016 9:18 AM by joe@bridgehouseindependentschool.co.uk

    Counting summarised fields that match a criteria

    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.

       

      Thanks in advance

       

      Joe

        • 1. Re: Counting summarised fields that match a criteria
          David Moyer

          Hi,

          I apologize for not addressing your precise issue, but I want to recommend the following as a way of cleaning and speeding up your code (from the first if you provided above) ...

           

          Let(

            [

              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.)

          1 of 1 people found this helpful
          • 2. Re: Counting summarised fields that match a criteria
            joe@bridgehouseindependentschool.co.uk

            Hi David

             

            Thank you for the tidy code. I have implemented this and it still works as it did so thanks for that.

             

            Any idea about the problem I have??? Without more detail, in a nutshell I want to count the occurrences of above, below and expected for each subject. So if out of 10 students it works out and shows (like in the screen prints) that 3 are above, 2 are expected and 5 are below I need it to summarise that in an already summarised layout. What it is doing at the minute is adding up the records individually an showing how many scores are above, expected or below so lets say in Art there have been 10 assessment windows for each pupil instead of showing which pupils are above, expected etc at the end of the 10 assessments its showing the figure for all the assessments. Its not using the summarised section in the sum or count function.

             

            Joe

            • 3. Re: Counting summarised fields that match a criteria
              philmodjunk

              I suggest describing your data model. How did you organize the data used to produce these values into tables and records? What relationships, if any are in place?

              • 4. Re: Counting summarised fields that match a criteria
                joe@bridgehouseindependentschool.co.uk

                Ok.

                 

                I have a central table called students. This stores all the vital data for each student. Each student has an Id (StudentID).

                For the assessments there is another table called AssessmentProgressScores this is linked via a field StudentIDfk, one to many. For each student they have an assessment score entered at certain intervals with either -2, -1 0 (below expected score) a 1 (expected score) or 2 or 3 (above expected score). There is a new record created for each student every time in the AssessmentProgressScores table.

                 

                I can summarise the data to show who is above, on or below expected progress for each subject using the calculation above. Its basically if their accumulative score is the same or bigger than the possible score they are on or above expected progress. This shows up with some conditional formatting using the red amber green system. All I want to do now is count how many students are above, on or below for each subject. This works in so much that I can get each student to show a summarised version with their overall score for the subject and then the progress indicator. But at the end of the subject summary I just cant get it to identify how many students fit into the above, on or below categories.

                 

                If you need any more info i'll happily explain more.

                 

                Joe