0 Replies Latest reply on Feb 19, 2014 2:14 PM by Mark Holtz

    null data falling out of table calculation on Gross Collection Rate dashboard

    Mark Holtz



      I've got a pretty complicated workbook. I've posted a version with 2 of our clients with all names redacted.

      The tab in question is the "Resolve Graph" view.

      I'll do my best to communicate the setup and our problem, but I imagine any helpers will have more questions.


      The concept of GCR and GRR are cumulative Gross Collection Rate and Gross Resolution Rate.

      In essence, charges created in a single month aggregate to form that month's "batch."

      This represents the total charges available to be collected/resolved.

      Payments received against the charge amount count as "collection" and any non-payment transactions (insurance write-offs, bad debt write-offs, credits, etc.) in addition to payments count towards the resolution total.


      We also capture the "aging bucket" in which the collection/resolution happens. The problem we're having is for the GRR by aging bins.


      For example, say we had $1,000 in charges created in all of November-2013.

      Then, we had payments of $100 and write-offs of $200 by the next month (Dec-13). At that point, our GCR would be 10% ($100/1,000) and our GRR would be 30% ($300/1,000).

      Then, the following month (Jan-14), we had added an additional $125 in payments and $250 in write-offs. Now our (cumulative) GCR would be 22.5% ([$100+125] / 1,000) and our (cumulative) GRR would be 67.5% ([100+200 + 125+250] / 1,000).


      However, on 12/1/14, not all dates of creation from the November-2013 "batch" had aged a full 30 days. So, we don't put them all into the batch. Each day in December, more dates of creation from the November batch reach "30 days old". We use a calculation in the various measures to wait to have at least 7 days in a batch (when we have a sufficient sampling) to report the GRR for that age bin. 


      But some clients have sporadic charge volume when they start up. What is happening is that our report shows the GRR correctly when we bring in the Creation Month, but because of our attempt to exclude Creation Months from age bins until the month has at least 7 days that qualify for the age bin, we somehow lose the measure for that bin.


      In terms of the graph in the attached file, the "Resolve Graph" view shows lines, but not bars for clientID = TRI1 (a new client who is ramping up). ClientID=MPS1 is fully ramped up and has been onboard for many months, so their overall average days easily passes the 7-day test for each batch.


      There are a lot calculations in the workbook. However, the only ones that I believe are involved in this issue are [DaysFromMonth], and then the various [Resolved Pct LT...] and the [Resolved Pct GT180] calculated fields.  We use the measures twice on the view, once broken by CreationMonth (to show the different lines) and once not broken by CreationMonth which is supposed to show the blended average of values in each aging bin.  I thought I would be able to figure out how to merge the first instance of MeasureValues by using a window_sum / window_sum in each of the Resolved Pct measures, but I got stuck.


      Would appreciate ANY insight. If you can tell me what exactly is going wrong, or more desirably, how to get client TRI1 to behave like MPS1, where all the columns that have lines ALSO have bars, I'd be grateful!