1 Reply Latest reply on May 9, 2017 7:13 PM by Okechukwu Ossai

    Avg. Time Calc based on Parameter "As Of Date"

    Nick Warchol
    Nick Warchol May 9, 2017 2:02 PM

      Hi All,

       

      We've received a client request to show a 1Day, 1Week, 1Month and 3Month average for event completion time for three specific events.  They would like those calculations to update based on the selection of a single day; this single day can be considered an "As Of" day.  So if 5/9/2017 was selected, the 1Day would be for 5/9/2017, the 1Week (7 calendar days) would be for the period 5/3/2017 - 5/9/2017, the 1Month (30 calendar days) would be for 4/10/2017 - 5/9/2017 and the 3Month would be a 90 calendar day avg.

       

      The avg. calculations we're using are based off of a parameter for the "As Of Date", but we're having an issue where we need to change the quick filter for the source date field in order for the avg. calculations to work properly.  The goal is to have the only user interaction be with the "As Of Date" parameter and not have to change a quick filter as well.

       

      Does anyone know if this is feasible or have another suggestion on how we may need to redesign the way we go about this?

       

      Attached is a packaged workbook which contains 6 months of historical data from 10/20/2016 - 3/20/2017.

       

      Thanks in advance for any help.

      Nick

        • 1. Re: Avg. Time Calc based on Parameter "As Of Date"
          Okechukwu Ossai
          Okechukwu Ossai Ambassador May 9, 2017 7:13 PM (in response to Nick Warchol)

          Hi Nick,

           

          You are almost there. I only made few changes to the average calculations.

           

          [1 Day Avg.]

          IF [Receive Date] = [As Of Date] THEN [Receive Time Calc] END

          [1 Week Ag.]

          IF [Receive Date] >= DATEADD('day', -6, [As Of Date]) AND [Receive Date] <= [As Of Date] THEN [Receive Time Calc] END

          [1 Month Avg.]

          IF [Receive Date] >= DATEADD('day', -29, [As Of Date]) AND [Receive Date] <= [As Of Date] THEN [Receive Time Calc] END

          [3 Month Avg.]

          IF [Receive Date] >= DATEADD('day', -89, [As Of Date]) AND [Receive Date] <= [As Of Date] THEN [Receive Time Calc] END

           

          Hope this helps. See attached workbook in version 9.2

          Ossai