1 Reply Latest reply on Aug 31, 2012 10:33 AM by John Ziebro

    Problem Calculating Recovery Rates

    John Ziebro

      Hi,

       

      I operate a statewide repossession company that does a high volume of vehicle repossessions. I am interested in calculating the recovery rate of my individual agents as well as recovery rates specific to my client.

       

      I have a csv file that I have exported from our database and have around 50,000 records since 2009. I have been able to figure out a calculation for the number of assignments by performing COUNT([Order Date]). I was also able to determine recoveries by doing the same thing for the recovery date; COUNT([Recovery Date]).

       

      The agent's recovery rate is determined by dividing the recovery by the number of orders. COUNT([Recovery Date]) / COUNT([Order Date]). Herein lies my problem and question. While the get a valid Domain when clicking on Describe for the Recovery Rate calculation, 52%, when I try to graph this quarter over time by agent, I essentially get a between 98 and 100% recovery rate ongoing. Obviously, this is incorrect. How do I get the agents individual actual recovery rate.  The field headers are as follows:

      Last 6 of VINRepo TypeStatusOrder DateRepo DateClose DateComplete DateBranchUpdatesRecovery AgentRecovery AddressRecovery CityRecovery StateRecovery ZipRecovery TimeStorage LocationClient NameLienholder NameYearMakeModel


      Attached file includes a screenshot of the graph... I feel uncomfortable sharing the data as it may have Non-public Information, NPI, contained in it and I have not scrubbed it for public use.

       

      Message was edited by: John Ziebro

        • 1. Re: Problem Calculating Recovery Rates
          John Ziebro

          I started to wonder if the fact that I was using the count of dates had something to do with this problem, so I used other fields. Still the same problem. From what I can tell of the COUNT definition, it discards NULL values in the csv file. So for example, every assignment has a Client Name attached to it, but if it is not repossessed it does not have a storage location. So a COUNT operations on a record that opened and was closed without being repossessed would yield 1 for COUNT[(Client Name)] and 0 for COUNT[(Storage Location)]. Based on that, in theory, I can then determine the number of orders and the number of recoveries then graph them over time. Additionally, I can divide the count of Storage Location by count of Client Name and get the recovery rate.

           

          Problem is, when I am graphing these numbers over time I am getting the exact same graph for Orders and Repos. On top of that, the recovery rate is remaining 100%.

           

          Any help would be greatly appreciated.