Calculate monthly payment using period of time. - Qlik Community

alec1982 · ‎2012-08-20

Hi,

I have a table that shows

ID Starting Date End Date Payment Date Payment Frequency

1 2012 05 01 2012 09 10 2012 05 01 10 Monthly

1 2012 05 01 2012 09 10 2012 06 01 10 Monthly

1 2012 05 01 2012 09 10 2012 07 01 10 Monthly

1 2012 05 01 2012 09 10 2012 08 01 10 Monthly

1 2012 05 01 2012 09 10 2012 09 01 10 Monthly

As you see the amount of every month is 10 and the last month has only 10 days in it only so the amount is wrong.

Is there anyway i can fix the amount of the last month based on days so it calculate correctly?

it came to my mind to build a table for the days in Months:

Load Intial:

Month Days

1 31

2 28

3 31

4 30

5 31

6 30
7 31

8 31

9 30

10 31

11 30

12 31

and then calculate it from there but I got lost so much...

Anybody can help?

Thxs.

Alec,

manojkvrajan · ‎2012-08-20

Data in the table looks the same excluding the Payment Date for all the records. Please provide more details.

alec1982 · ‎2012-08-20

Hi,

Thxs for the reply.

That's true.

If you look at the last raw, you will notice that total days in this month is 10 days.

so the amount of $10 is wrong.

I need to find a method that will look at the last month and check if it is a full month or not and in case it is not then calculate the amount based on the days.

like in our sample case above, The amount should be around $3.35 saying that we have only 10 days in this month and the monthly rate is $10.

Thxs,

swuehl · ‎2012-08-20

Maybe like

LOAD *,

if(monthstart([End Date]) = monthstart([Payment Date]),

day([End Date])/day(monthend([End Date]))*Payment,Payment) as PaymentNew

resident YourTable;

alec1982 · ‎2012-08-21

Hi,

Thank you for your reply.

the answer is very helpfull but there is somethng wrong that I am doing!

Like I have one of the IDs has two periods and on the final period is not doing the calculation.

I have attached an excel file of the output.

I appreciate your help!

manojkvrajan · ‎2012-08-21

What is the correct value for the wrong calculation? Should the payment be multiplied by the # of days? However, it doesn't make any sense to me. Please share the sample calculation with all possible scenarios and let me see how can I help...

For example

ExpenseStartDate = 2010-12-17

ExpenseEndDate = 2015-09-30

ExpenseAmount = $12375

So should the chart compute the # of days between ExpenseStartDate and ExpenseEndDate multiplied by the per day Payment value?

I mean ....

No. of Days (between 2010-12-17 and 2015-09-30) = 1749

Expense Amount per day = $12375 / 30 = $ 412.5

Payment = 1749*412.5= $ 721462.5