2 Replies Latest reply on Dec 17, 2012 5:44 PM by Matthew Petrich

    Grouping and summing payment transaction by a date/year other than check date.

    Matthew Petrich

      Hello everyone,

      I have a payment transaction table from a large number of insurance claims.  For each payment transaction, I have two dates.  The Date Opened and the Check Date for the payment.  I am trying to put together a view that would group all the distinct claims by the Month/Year of Date Opened and then for that same date period, sum up all the payments.

       

      I can easily get the distinct claim count by Date Opened but I am really struggling figuring out how to group and sum my payments by Date Opened.

       

      Here is an example;

      Claim ID     Date Opened    Payment Amount     Check Date

      1                   1/1/12                    $50                        2/1/12

      2                   1/10/12                 $100                       1/10/12

      3                   2/2/12                    $25                        2/10/12

      4                   2/10/12                  $125                      3/1/12

       

      For the data above, I would like the report to like this;

      Month/Year          # of Claims Opened     Total Paid for Month/Year

      January 2012                    2                              $100

      February 2012                  2                               $75

       

      Again, the difficult part for me is grouping and summing the payments by a date/year other than check_date.

      Thank you for any help you can provide!

        • 1. Re: Grouping and summing payment transaction by a date/year other than check date.
          Dean Hewitt

          Hello Matthew,

           

          This can be done by blending the data.  First you duplicate the data source.  Rename them Date Opened and Check Date  Then establish a relationship between the Date Opened and the Check Date at the year and month level. Now build the first two colums using the fields from the Date Opened source and then sum the total paid using the field from the Check Date source.I have included a workbook with this example.

          1 of 1 people found this helpful
          • 2. Re: Grouping and summing payment transaction by a date/year other than check date.
            Matthew Petrich

            Hi Dean,

            Thank you very much for your response.  Your response and example worked perfectly for the example that I gave you and I was able to re-create your solution in my workbook.  I did run into an issue around filtering though.  For the example that I provided, let's say I wanted to apply a filter to only see the claims that were litigated and let's say one of the claims that opened in January 2012 was litigated and one was not.  When I applied the litigation filter, the number of claims that opened in January 2012 properly showed only one claim but the filter did not get properly applied to the total paid field.

            Any ideas on how to get the filters to work properly?

            Thank you again for your help!