2 Replies Latest reply on Feb 12, 2018 11:41 AM by mircea.rusu.0

    Average calculation over a period of time

    mircea.rusu.0

      Hello,

       

      I have the following workbook that has a simple calculation of defects average (submitted vs. resolved defects) over the entire [Date] interval, using the following formula:

      Resolved_prc = [Resolved_avg]/[Submitted_avg] (this is a percentage)

      where

      Resolved_avg = SUM([Resolved1]) / (DATEDIFF('month',MIN([Date]),MAX([Date])))

      Submitted_avg = SUM([Submitted1]) / (DATEDIFF('month',MIN([Date]),MAX([Date])))

       

      NOTE: there may be gaps in the data, I.e for some months there may not be any "resolved" but some "submitted" and for other months there may not be any "submitted" but some "resolved", that's why I am using the above DATEDIFF approach.

      So far everything works correctly, no problem.

       

      However, I need to display this calculation for every month, over the entire period of time. So I have to calculate this value for previous months as well and generate a line chart.

      If I add [Date] in the Columns I am getting wrong results...

       

      Please advise.

       

      Regards,

      M.R.

        • 1. Re: Average calculation over a period of time
          Deepak Rai

          Pl Check Screenshot and attached.

          Thanks

          Deepak

           

          1 of 1 people found this helpful
          • 2. Re: Average calculation over a period of time
            mircea.rusu.0

            Thank you so much Deepak for looking into this.

            The problem is that the blue line refers to a specific month but what I am trying to achieve is a line that calculates an average for all previous months... I other words, my chart is a snapshot of current month. I would like the same calculation for all previous months.

            I mean to plot something like this:

             

            Current month:

            Resolved_avg = SUM([Resolved1]) / (DATEDIFF('month',MIN([Date]),MAX([Date])))

            Submitted_avg = SUM([Submitted1]) / (DATEDIFF('month',MIN([Date]),MAX([Date])))

             

            then previous month (not a real formula just the concept):

            Resolved_avg = SUM([Resolved1]) / (DATEDIFF('month',MIN([Date]),MAX([Date] - 1 month)))

            Submitted_avg = SUM([Submitted1]) / (DATEDIFF('month',MIN([Date]),MAX([Date] - 1 month)))

             

            then two months before

            Resolved_avg = SUM([Resolved1]) / (DATEDIFF('month',MIN([Date]),MAX([Date] - 2 months)))

            Submitted_avg = SUM([Submitted1]) / (DATEDIFF('month',MIN([Date]),MAX([Date] - 2 months)))

            ...................etc....    etc....

             

            Resolved_avg = SUM([Resolved1]) / (DATEDIFF('month',MIN([Date]),MAX([Date] - n months)))

            Submitted_avg = SUM([Submitted1]) / (DATEDIFF('month',MIN([Date]),MAX([Date] - n months)))