5 Replies Latest reply on Oct 4, 2018 6:20 PM by lei.chen.0

    Month over Month Comparison

    Sarper Beyazyurek

      Hi - I have a 6 months loan data by report date field. For the most part same loan repeat each month but each month new loans are added and some loans liquidated. is there a filter or calculation that I can keep the loan population constant so I only look at the surviving loans ?

       

      thanks!

        • 1. Re: Month over Month Comparison
          lei.chen.0

          Hello Sarper,

           

          Since there is no workbook or sample attached, I assume your data has three fields.

           

          Dimension: loan_id, loan_date

          Measure: load_amount

           

          Also I assume that only "load 1" is the pattern you want to keep.

           

          loan 2-6: liquidated

          load 7-11: new

           

          Thus, a calculated field like,

           

          { FIXED [load_id]: COUNTD(MONTH([loan_date]))} = 6

                                                                                                                                                                                

           month 1month 2month 3month 4month 5month 6
          loan 1datadatadatadatadatadata
          loan 2datadatadatadatadatano data
          loan 3datadatadatadatano data no data
          loan 4datadatadatano data no data no data
          loan 5datadatano data no data no data no data
          loan 6datano data no data no data no data no data
          loan 7no data datadatadatadatadata
          loan 8no data no data datadatadatadata
          loan 9no data no data no data datadatadata
          loan 10no data no data no data no data datadata
          loan 11no data no data no data no data no data data

           

          would work for you.

           

           

          Regards

          Lei

          • 2. Re: Month over Month Comparison
            Naveen B

            Hi Sarper,

             

            Could you plz attach a sample workbook with mockup data so that it will be useful to decipher your problem

             

            BR,

            NB

            • 3. Re: Month over Month Comparison
              Sarper Beyazyurek

              Hi Lei - thanks for your reply. What i am trying to do is this: If i have 100 loans in month 1 and each month some loans are falling from the population but each month new loans are added into the data i want to be able to only look at the loans performance that survived month over month. My goal is to look at at static portfolio and determine if delinquencies are going up or down. The analysis gets diluted when there are new loans added to the data. Does that make sense?

              • 4. Re: Month over Month Comparison
                Sarper Beyazyurek

                Hi Naveen - i cant attach data as its sensitive information. Best way to describe is say i have a data table for 6 months of data. Loan IDs are repeated and each month we add new loans and some loans are removed month over month. When loans are coming in and out its hard to measure the performance of the pool. What would be the best way to set a filter so each month we compare to prior month for matching loans only?

                • 5. Re: Month over Month Comparison
                  lei.chen.0

                  Hello Sarper,

                   

                  Your requirements are well described however data format is not, which prevent me from going into further advices.

                   

                  Please have a look at my previous reply and see if it fits your situation.

                   

                   

                  Regards

                  Lei