3 Replies Latest reply: Nov 23, 2016 9:01 AM by Rima Hajou RSS

    Show the amount paid by all the customers on a timeline by month

    Rima Hajou

      Hello,

      I am trying to do a timeline chart, where I can show the amount left to be paid by every customer, here is a table that shows my data:

       

      ID | Maturity Loan Date | Disbursement Date | Loan Amount | Loan Duration(months)

      1   | 10/2020                 | 10/2016                  | 20,000$        | 48

      2   | 09/2021                 | 9/2013                    | 9,800$          | 96

      3   | 2/2018                | 7/2015                    | 3000$             | 31


      By this data, I can calculate The amount paid every month ( Loan Amount / Loan duration) supposed that every customer will pay his debt monthly.

      How can we achieve to see a Bar Chart that shows the amount collected every month from all the customers?




      Thanks,

      Rima

        • Re: Show the amount paid by all the customers on a timeline by month
          Marco Wedel

          Hi,

           

          maybe one solution could be:

           

          tabLoans:
          LOAD ID,
              Date#([Maturity Loan Date],'MM/YYYY') as [Maturity Loan Date],
              Date#([Disbursement Date],'MM/YYYY') as [Disbursement Date],
              Money#([Loan Amount]) as [Loan Amount],
              [Loan Duration(months)]
          Inline [
          ID | Maturity Loan Date | Disbursement Date | Loan Amount | Loan Duration(months)
          1  | 10/2020            | 10/2016          | 20,000$    | 48
          2  | 09/2021            | 9/2013            | 9,800$      | 96
          3  | 2/2018            | 7/2015            | 3000$      | 31
          ] (delimiter is '|');
          
          tabLoanMonth:
          LOAD ID,
              Date(AddMonths([Disbursement Date],IterNo()-1),'MM/YYYY') as Month,
              Money([Loan Amount]/[Loan Duration(months)]) as MonthlyAmount
          Resident tabLoans
          While IterNo() <= [Loan Duration(months)];
          

           

          QlikCommunity_Thread_240505_Pic1.JPG

           

          hope this helps

           

          regards

           

          Marco