6 Replies Latest reply: Nov 13, 2012 7:16 AM by Tim Hop RSS

    calculation in script

      Hi all,

      I am trying to do a cakculation in the script and give it a new field name but am unsure on how to do it.

      Here is what i am trying to do


      I have lots of fields that i want to subtract one from the other.


      Y3_Autumn_Reading_APS - PupilResultsY2SummerReadingAPS

      Y3_Autumn_Writing_APS - PupilResultsY2SummerWritingAPS

      Y3_Autumn_Mathematics_APS - PupilResultsY2SummerMathematicsAPS

      Y3_Autumn_ICT_APS - PupilResultsY2SummerICTAPS

      Y3_Autumn_Science_APS - PupilResultsY2SummerScienceAPS

      Y4_Autumn_Reading_APS - PupilResultsY2SummerReadingAPS

      Y4_Autumn_Writing_APS - PupilResultsY2SummerWritingAPS

      Y4_Autumn_Mathematics_APS - PupilResultsY2SummerMathematicsAPS

      Y4_Autumn_ICT_APS - PupilResultsY2SummerICTAPS

      Y4_Autumn_Science_APS - PupilResultsY2SummerScienceAPS

      Y5_Autumn_Reading_APS - PupilResultsY2SummerReadingAPS

      Y5_Autumn_Writing_APS - PupilResultsY2SummerWritingAPS

      Y5_Autumn_Mathematics_APS - PupilResultsY2SummerMathematicsAPS

      Y5_Autumn_ICT_APS - PupilResultsY2SummerICTAPS

      Y5_Autumn_Science_APS - PupilResultsY2SummerScienceAPS

      Y6_Autumn_Reading_APS - PupilResultsY2SummerReadingAPS

      Y6_Autumn_Writing_APS - PupilResultsY2SummerWritingAPS

      Y6_Autumn_Mathematics_APS - PupilResultsY2SummerMathematicsAPS

      Y6_Autumn_ICT_APS - PupilResultsY2SummerICTAPS

      Y6_Autumn_Science_APS - PupilResultsY2SummerScienceAPS

      Y3_Summer_Reading_APS - PupilResultsY2SummerReadingAPS

      Y3_Summer_Writing_APS - PupilResultsY2SummerWritingAPS

      Y3_Summer_Mathematics_APS - PupilResultsY2SummerMathematicsAPS

      Y3_Summer_ICT_APS - PupilResultsY2SummerICTAPS

      Y3_Summer_Science_APS - PupilResultsY2SummerScienceAPS

      Y4_Summer_Reading_APS - PupilResultsY2SummerReadingAPS

      Y4_Summer_Writing_APS - PupilResultsY2SummerWritingAPS

      Y4_Summer_Mathematics_APS - PupilResultsY2SummerMathematicsAPS

      Y4_Summer_ICT_APS - PupilResultsY2SummerICTAPS

      Y4_Summer_Science_APS - PupilResultsY2SummerScienceAPS

      Y5_Summer_Reading_APS - PupilResultsY2SummerReadingAPS

      Y5_Summer_Writing_APS - PupilResultsY2SummerWritingAPS

      Y5_Summer_Mathematics_APS - PupilResultsY2SummerMathematicsAPS

      Y5_Summer_ICT_APS - PupilResultsY2SummerICTAPS

      Y5_Summer_Science_APS - PupilResultsY2SummerScienceAPS

      Y6_Summer_Reading_APS - PupilResultsY2SummerReadingAPS

      Y6_Summer_Writing_APS - PupilResultsY2SummerWritingAPS

      Y6_Summer_Mathematics_APS - PupilResultsY2SummerMathematicsAPS

      Y6_Summer_ICT_APS - PupilResultsY2SummerICTAPS

      Y6_Summer_Science_APS - PupilResultsY2SummerScienceAPS


      AS NewField


      Is this possible?

        • Re: calculation in script
          Stefan Wühl

          Are all these fields located in one table? Do you want to add up each line or ...?

            • Re: calculation in script

              Hi, yes they are all in the same table. i want to calculate line by line. this data is related to schools, so each student will have a figure against each of the above fields. i want to keep it that way so i can get a figure for each student. if that makes sense. ?


              .So student A

              Y6_Summer_ICT_APS - PupilResultsY2SummerICTAPS


              If Y6_Summer_ICT_APS has a figure for the student of 25


              PupilResultsY2SummerICTAPS has a figure of 15

              then i would want a new figure of 10 for this student (25-10)


              Does that make sense?

                • Re: calculation in script
                  Stefan Wühl

                  Not sure if I understand, you want something like



                  Y3_Autumn_Reading_APS - PupilResultsY2SummerReadingAPS as ReadingResult,

                  Y3_Autumn_Writing_APS - PupilResultsY2SummerWritingAPS as WritingResult,


                  FROM ...;



                    • Re: calculation in script

                      Hi, no, i wanted to do all the calculations then save it as ONE new fieldname rather than several. but i am not sure if that is possible?





                      Y3_Autumn_Reading_APS - PupilResultsY2SummerReadingAPS ,

                      Y3_Autumn_Writing_APS - PupilResultsY2SummerWritingAPS as NewField,


                    • Re: calculation in script


                      Y3_Autumn_Reading_APS - PupilResultsY2SummerReadingAPS AS FieldName,

                      Y3_Autumn_Writing_APS - PupilResultsY2SummerWritingAPS as  AsYouWant



                        • Re: calculation in script

                          Hi, ok i have done sort of what i wanted. i have created new fields per subject / term as below


                          LOAD PupilID,





                                 ))))                                                                            as AutumnReadingProgress,





                                 ))))                                                                             as SpringReadingProgress



                          Now i want to create a chart with an expression to do the following..


                          if pupilyeargroup ='4' and term = 'Autumn' then display a count of pupilID that have a  AutumnReadingProgress score of 4 or more.

                          if pupilyeargroup ='5' and term = 'Spring' then display a count of pupilID that have a  AutumnReadingProgress score of 8 or more etc.


                          can anyone help with writing this please?