3 Replies Latest reply: May 15, 2014 10:02 AM by KarenG _ RSS

    Creation of Summary field problem

    KarenG _

      Hi

       

      I am trying to ascertain the average of two fields within my summary.  Initially i used the 'Average' function, but that is not giving back the result that i am having to calculate to. 

      I have a 'count' column - which is a measure - which is all the instances that relate to a certain operator

      i have a 'time to complete' - which is a calculated field in the table and in time format - which is the lenght of time that operator took to resolve the issue.

       

      an example of my problem:

       

      Operator                            1   

      Count                                83          

      Time                                 18886                                                           

      Average in Monarch             310    

      Average in Excel                  228

       

       

      The average column has the formula AVG()/60

      but if you divide 18886/83 (time/count)you arrive at 228

      I tried to use the time/count in my summary formula but as 'count' is a measure it wont allow me to.

       

      Can anyone please help

       

      Kind Regards

        • Creation of Summary field problem
          Nigel Winton

          Karen

          You can calculate the number of instances in the summary by using a calculated field in the table that only has 1 as its formula and totalling this for the operator with a sum formula in the summary. Then you should be able to use that field to calculate the average as you have already tried, thereby avoiding having to use the count field. You can hide the new table field if you need to.

           

          Regards

           

          Nigel

          • Creation of Summary field problem
            Grant Perkins

            Karen,

             

            When working with times, especially when doing more than time difference calculation and even then being careful, it is best to convert you values to seconds, sum the number of seconds, calculate the average and then convert back to a time format  - probably HH:MM:SS in most but not all cases.

             

            It's not easy to be sure of what you are dealing with from the information provided.

             

            Is the Time value of 18886 in minutes?

             

            Looks like it might be.

             

            On the other hand your formula refers to a field by a name that suggests ITS value is in hours and minutes. It may not be in hours and minutes but it would be good to check.

             

             

            Grant

              • Creation of Summary field problem
                KarenG _

                Hi

                 

                thanks for the replies.. i managed to do it by using Sum()/COUNT(*)/60.. took a lot of trial and error though.

                One more question... on one of the columns i am exporting, even though i have set the format to 'numeric' but it is still exporting with the leading '

                how can i stop this?

                 

                Karen