2 Replies Latest reply on Jun 21, 2016 4:54 AM by Oliver Shingfield

    Divide by Number of records for a certain month and year

    Oliver Shingfield

      Hello guys,

       

      I am trying to make a calculated field to divide by number of records for a certain month and year,

      This is what I've got so far:

      ((SUM([HoursInSeconds]) + SUM([MinsInSeconds]) + SUM([Seconds])) / 3600

      This calculated the amount of time and puts it into hours. I then have a field that has a ton of records across 5 years. I only want it to divide by records from April 2016.

      So would look something like this:

      ((SUM([HoursInSeconds]) + SUM([MinsInSeconds]) + SUM([Seconds])) / 3600 IF(Date) = April 2016 THEN / Number of records

       

      Something like that?

      I am inexperienced when using calculated fields, can someone help please?

       

      Kindest Regards,

       

      Oli

        • 1. Re: Divide by Number of records for a certain month and year
          John Sobczak

          I'm not exactly sure what you mean but one interpretation of what you are saying could be:

           

          (SUM(IF(Date) = April 2016 THEN [HoursInSeconds] end) + SUM(IF(Date) = April 2016 THEN [MinsInSeconds] end) + SUM(IF(Date) = April 2016 THEN ([Seconds]) end)) / 3600

           

          I don't know what you mean by number of records though.

          • 2. Re: Divide by Number of records for a certain month and year
            Oliver Shingfield

            Sorry, I realise now I wasn't very clear with my description.

             

            So say for example, I have two datasets, one for number of incidents and one for Time spent raising these incidents.

             

            The number of incidents is the count of the reference numbers.

             

            What I am trying to do is add up the total number of time spent on the incidents divided by the number of records of incidents we have.

            The number of records part is just 1 per incident. So I want the calculation to add up the time and divide by the total number of records.

            I need this, but for April 2016 only. Currently, I have incidents going back to 2011, So I want to 'filter' the number of records within the calculation to only add up the number of records for April 2016.

             

            So SUM(All of the seconds) / 3600 to convert it back to hours, / by number of incident records for April 2016 only.

             

            It's quite hard to explain and as I am on a work machine it's hard for more to attach a workbook.

             

            If you can help that would be great as I have hit a brick wall

             

            Kind Regards,

             

            Oli