4 Replies Latest reply on Oct 10, 2019 8:10 AM by Ciara Brennan

    Creating a Monthly Average of One Field, Based on Unique ID in Another

    Christopher Reis

      Hello, I have a data set like the one below (the report will run each month, and that data will be appended to the master report).

       

      For each Report Month, I'll need to display an average Case Age for each distinct Case ID. So for example, the Report Month of October 2019 has 4 distinct Case ID's (56301, 49888, 55441, 66210) with Case Age's of 35, 59, 40 and 15 days (respectively) for an average Case Age in October of 37.25 days.

       

      I've been experimenting with approaches like...

       

      { FIXED [CASE ID], [Report Month]: MIN([Case Age]) }

       

      ...Which returns the correct sum of Claim Age, but when I try to go one step further to complete the average, the calculation falls apart...

       

      { FIXED [CASE ID], [Report Month]: MIN([Case Age]) } / {COUNTD([Case ID])}

       

      I'd appreciate any help.

       

           

      Case IDCase OpenedCase ManagerFollow Up ReasonReport MonthCase Age
      563018/27/2019Allen, JanetCheck-In9/1/20195
      563018/27/2019Allen, JanetPaperwork9/1/20195
      563018/27/2019Allen, JanetBirth Certificate9/1/20195
      498888/3/2019Jefferson, TheresaDeath Certificate9/1/201929
      554418/22/2019Welsh, JillianCheck-In9/1/201910
      554418/22/2019Welsh, JillianBirth Certificate9/1/201910
      563018/27/2019Allen, JanetCheck-In10/1/201935
      563018/27/2019Allen, JanetPaperwork10/1/201935
      563018/27/2019Allen, JanetBirth Certificate10/1/201935
      498888/3/2019Jefferson, TheresaDeath Certificate10/1/201959
      554418/22/2019Welsh, JillianCheck-In10/1/201940
      554418/22/2019Welsh, JillianBirth Certificate10/1/201940
      662109/16/2019Jefferson, TheresaPaperwork10/1/201915