1 Reply Latest reply on Mar 31, 2016 2:47 PM by diego.medrano

    Credit evolution performance - computing with different sets of rows

    Francesco Dall'Asta

      Hi all,

      I would like to produce a credit performance indicator that's really simple in theory:  Sum of open Credit  / Sum of original credit (invoice).

       

      In particular, I have a dataset that is at invoice level. Data comprehend the amount of invoice, the timeof emission, the amount of correspondent credit and the time of observation of credit (and a measure which represents the number of months after which we are observing the credit from the emission). I attach also a packaged workbook with a sample dataset.

       

      DATASET structure

      Invoice #Invoice AmountInvoice emission timeCredit amountCredit observation time
      Time N
      11111100Jan 2015100Jan 20150
      11111100Jan 201580Feb 20151
      11111100Jan 201550Mar 20152

       

      The invoices are repeated in the dataset the times their credit is observed in different time.  Because of this, to compute the exact amount of revenues from the invoices I managed to build a LOD calculation which takes the average for each invoice number on order to have the sum of each invoice and not the sum of each row that countains that invoice.

       

      Invoice total amount : { FIXED [Invoice #] : AVG ([Invoice amount]) } 

       

      After that I can breakdown the credit amount by month of credit observation to see the credit.

      For each Month of credit observation (or Time N), I would like to have the metioned ratio (credit/original invoice amount).

       

      The problem here is that since each month of credit observation I have less rows (because of the invoices being payed month by month), I cannot properly compute that ratio at aggregated level. Given a set of invoices I would like to monitor them in each month of credit observation. However, given that some invoices have being payed, I lose some invoices and cannot run up to the original total invoice amount when I filter for the month of credit observation.

       

      I would need the numerator to refer to the credit of some invoices in that month and the denominator to refer to the original invoices amount (that comes from another set of rows (or invoices).  I made numerous attempts with IF and LOD calculation but I did not find any interesting solution.

       

      Basically, I need to dynamically change the set of rows I am referring to. Or another method I am not aware of.

       

      Please I need HELP!! Sample Tableau workbook attached.


      Many Thanks!!

       

      Francesco