3 Replies Latest reply: May 15, 2014 10:07 AM by Data Kruncher RSS

    Summary Average GPA

    sxschech _

      I've created several summaries to view the data by various levels of detail. I also subtotal by Status.  I have the totals and Avg GPA. The Avg GPA seems fine for the first grouping by Status, but then continues displaying the same exact Avg GPA for all the other groupings even though the totals are different. The totals are summing up properly and subtotaling on each change in the key value Status, but the GPA doesn't. GPA seems fine for Status of App, but shows the exact same numbers for the Status of Acc and Enr.

       

      Monarch version 8.01

       

      Example: (Numbers displayed aren't actual)

       

      Population     Status     College     Total    AvgGPA

      F                 App         LA           100     3.31

      F                 App         SC            75     3.25

      F                 Subtotal                 175     3.28

      F                 Acc         LA            60     3.31

      F                 Acc         SC            48     3.25        

      F                 Subtotal                 108     3.28

      F                 Enr         LA             0     3.31

      F                 Enr         SC             0     3.25

      F                 Subtotal                   0     3.25

      /code

        • Summary Average GPA
          Data Kruncher

          Expand the number of decimal places (to maybe 5 places?) for the AVG GPA to see if they're really the same values, or just coincidentally close in value, and the rounding to two decimal places is yielding an illusion of inaccuracy.

           

          All the same, this does feel like something, a problem, that I've seen before. But more detail is needed - a sample of your table values for the fields invovled - to test/model properly.

            • Summary Average GPA
              sxschech _

              Hi Data Kruncher,

               

              Thank you so much for your response.  Your sugestion to provide sample values led me to the solution.  I noticed that the data were showing GPA regardless of the Tot value which meant I needed to add an additional if condition to make GPA null where the value of Tot = 0 as the data came over with the GPA regardless of 0 or 1 in the Tot field.  And yes it is familiar as you graciously assisted me with the original formula.  Not sure why I didn't notice that the GPA's were there before, so obviously that is why they calculated the same for the entire data set.  For reference, here is the revised formula:

               

              if(tot=0,1/0,if(isblank(GPA1),1/0,Val(GPA1)))

               

              I think I'll see about blanking out GPA on the back end, but leave the formula in place so that if it happens again, Monarch will know how to properly calculate.

                • Summary Average GPA
                  Data Kruncher

                  Um... you're welcome! Glad to help! Next time I'll have to work a little harder to offer a solution though.

                   

                  Seriously though, this is a great example of how easy it is to transform Monarch from a simple data processor into a tool that handles rules and exceptions without extra manual effort.