2 Replies Latest reply on Sep 25, 2018 1:26 AM by Nicholas Barter

    Table Calculations with multiple starting points

    Nicholas Barter

      Hi

       

      I'm trying to calculate bad rates over multiple periods.

       

      So, for each observation period I can divide the accumulating number of accounts that have a "D" (Default) Account Status, by the number of accounts that are observed (with a "G" Account Status) at the Parameter called Observation Period: RUNNING_SUM(COUNTD([Agreement Number of Future Defaults]))/[Number of Live Agreements at Observation]

       

      So I can get something like the below.

       

      Month of PeriodNo of Months% Of Defaults
      Sep-1400.00%
      Oct-1410.93%
      Nov-1421.92%
      Dec-1432.91%
      Jan-1543.77%
      Feb-1555.09%
      Mar-1566.42%
      Apr-1577.34%
      May-1588.07%
      Jun-1599.06%
      Jul-15109.85%
      Aug-151110.91%
      Sep-151212.04%

       

      However, I would like to have September 2014 data alongside October 2014 and November 2014 etc, ending up something like this, which I'll then be able to chart:

        

      Number of Months from Start% Of Defaults from Start
      Sep-14Oct-14Nov-14Dec-14
      00.00%0.00%0.00%0.00%
      10.93%0.95%0.91%0.83%
      21.92%1.91%1.77%2.32%
      32.91%2.80%3.23%3.63%
      43.77%4.20%4.44%4.59%
      55.09%5.47%5.36%5.30%
      66.42%6.43%6.09%6.25%
      77.34%7.19%7.06%7.27%
      88.07%8.21%8.03%8.28%
      99.06%9.10%9.07%9.41%
      109.85%10.18%10.16%10.66%
      1110.91%11.33%11.38%11.32%
      1212.04%12.54%12.05%12.51%

       

      I'm struggling to get this working. Can someone please help

       

      I've created some dummy data to share this problem. Hopefully someone can help (or put me out of my misery and tell me it's impossible)! I'm using 2018.1

        • 1. Re: Table Calculations with multiple starting points
          Shinichiro Murakami

          Difficult to understand the logic. Could you explain how the below numbers are calculated for example

           

           

          Thanks,

          Shin

          • 2. Re: Table Calculations with multiple starting points
            Nicholas Barter

            This is calculated by the calculation: accumulative number of defaults / [Number of Live Agreements at Observation], where:

             

            Accumulative number of defaults = RUNNING_SUM(COUNTD([Agreement Number of Future Defaults]))

             

            Where [Agreement Number of Future Defaults] =

             

            IF [Defaults in Future?] = "Yes"  //

            AND [Default Month] = [Period]

            THEN [Account Number]

            END

             

            An account is deemed to default if they have a default month attached to its record - I've essentially created a LOD calculation before importing the data. (So accounts without a [Default Month] do not ever default.) The above calculation will return the [Account Number] if the account defaults in that month (this probably needs to change to get my problem working).

             

            The [Number of Live Agreements at Observation] =

             

            (LOOKUP(ZN(COUNTD(

            {FIXED [Account Number] : MAX(IF [Start Month]<=[Observation Period] AND [Last Month on Record]>=[Observation Period] THEN [Account Number] END) }

            )), FIRST()))

             

            This returns distinct count of the [Account Number] if the account is present at the [Observation Period] parameter.

             

            So for September, month 1; accumulative number of defaults / [Number of Live Agreements at Observation] = 14 / 1512 = 0.93% and month 12 = 182 / 1512 = 12.04%

             

            Maybe in one sentence, this is the total number of accounts that have defaulted up to that point, expressed as a percentage of the cohort that were present at the Observation Period,

             

            The full numbers can be seen on the "Future Defaults (Table) (all accounts)" sheet, which you can then move the [Observation Period] parameter through the months.

             

            Hope that makes sense!