Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

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

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

1 Solution

Accepted Solutions

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

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

3 Replies

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

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

Not applicable

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

Hello Marco,

Thanks for the solution, I implement your solution on my data , but the bar chart is showing the screenshot below, how can I aggregate the amount of every month into one record?

todel.png

Thanks!

Not applicable

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

Don't worry! I solved it , I added this 2 lines to the script

   Year(Date(AddMonths(DisbursementDate,IterNo()-1),'MM/YYYY')) as YearLoanIncome,

        Month(Date(AddMonths(DisbursementDate,IterNo()-1),'MM/YYYY')) as MonthLoanIncome,

and then added the 2 to a drill down master item.

Thanks Again Marco

Community Browser