Creating a Table of Payment Breakdown

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

This has been very challenging for me. I need to create a table that calculates the monthly amount for a given cost schedule (Table 1) and for a given set of due dates (Table 2).

Basically from something like this:

Record ID	Start Date	End Date	Amount Due	Schedule Type
ZZZ	1/1/2018	12/31/2018	500	Quarterly

I need to create this:

Record ID	Payment Date	Amount Paid
ZZZ	1/1/2018	500
ZZZ	4/1/2018	500
ZZZ	7/1/2018	500
ZZZ	10/1/2018	500

DATA PROVIDED:

Table 1 shows the Start Date and End Date of the amount due for different records. The Schedule Type tells whether it is “Monthly” or “Quarterly”. So for example 100ABC there is a cost of 500 every month from 9/1/2017 to 3/31/2018. For 200AAA, there is a cost of 300 every quarter from 4/1/2017 to 3/31/2018. [Schedule Type] is also what links Table 1 to Table 2.

Table 2 shows the actual Due Dates. The Due Dates are in the format of MM/DD. So “Monthly” is simply 1^st day of each month for each year until the End Date. For Quarterly_type01 it is 01/01 (Jan 1), 04/01 (April 1), 07/01 (July 1), 10/01 (October 1) of each year until the End Date. Quarterly_type02 has specific dates also.

REQUIRED OUTPUT:

So what I was trying to do at first is create a list of dates based on those Due Dates by attaching a year (starting from perhaps 2016 until 2020). Then from the data on Table 1 cost schedule, I have to split the schedule and map them onto the Due Dates.

So far I have been failing miserably. What makes it even trickier is when the Start Date or End Date does not line up properly with the Due Dates. Record ID=300DEF and Record ID=400MMM are good examples. For those instances the amount for the partial month (or partial quarter) has to be prorated based on the number of days.

The tab EXPECTED OUTPUT on the attached excel file is the desired generated table. 3 fields… [Record ID], [Payment Date], [Amount Paid]. [Payment Date] is basically just the Due Dates attached to a year. Any guidance?

Re: Creating a Table of Payment Breakdown

andrei_delta — Fri, 13 Apr 2018 14:13:01 GMT

Hi,

Is this what you are looking for?

Regards,

Andrew

topic Re: Creating a Table of Payment Breakdown in QlikView

Creating a Table of Payment Breakdown

Re: Creating a Table of Payment Breakdown