Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
markramiro
Creator II
Creator II

Payment Schedule

I have a table resembling a payment schedule:

IDStart DateEnd DateFrequencyCost
1231/1/201812/31/2020Monthly50
4562/1/201812/31/2020Annually300
7893/1/201812/31/2020Quarterly120

I then created a Pivot chart where each column is a Year-Month (see attached) like below. Please see the Load script in the file.

ID2018-Jan2018-Feb2018-Mar2018-Apr2018-May2018-Jun2018-Jul2018-Aug2018-Sep2018-Oct2018-Nov2018-Dec2019-Jan2019-Febetc....
123505050505050505050505050505050
4560300000000000003000
7890012000120001200012000120

The above is the desired result. In my file, you will see where ID=456, the amount of 300 is repeating each month. Same case where ID=780, 120 is repeating each month. ID=123 is fine because the Frequency is "Monthly". I am so far unable to factor the [Frequency] into the particular months that the Costs should reflect on the pivot chart.

If the [Frequency] for a record is "Annually", I want the Cost to reflect on the 1st month, zeros the next 11 months, and then reflect the cost on the 13th month and so forth (i.e. first month every 12 months). If the [Frequency] for the record is "Quarterly", I want the Cost to reflect on the 1st month, zeros on the 2nd and 3rd month, and then reflect the cost on 4th month and so forth (i.e. first month every 3 months).

1 Solution

Accepted Solutions
sunny_talwar

May be try changing Calendar script to this

Calendar:

load Distinct MonthStart(tempDate) as %DateKey,

Floor(MonthStart(tempDate)) as DateNum,

Year(tempDate)&'-'&Month(tempDate) as YearMonth,

Year(tempDate) as CostsYear,

1 as MonthMultiplicator

resident tempDates;


LOAD recID,

Monthstart(AddMonths([Start Date],iterno()-1)) as %DateKey,

Cost as MonthlyCost

Resident PAYMENTS

While Monthstart(AddMonths([Start Date],iterno()-1)) <= If(Floor(MonthEnd([End Date])) = [End Date], Monthstart([End Date]), Monthstart([End Date], -1));

View solution in original post

6 Replies
sunny_talwar

Try this

=If(Frequency = 'Annually',

If(SetDateYear([Start Date], Year(Today())) = SetDateYear(Date#(YearMonth, 'YYYY-MMM'), Year(Today())), Sum(MonthlyCost)),

If(Frequency = 'Quarterly',

If(SetDateYear([Start Date], Year(Today())) = SetDateYear(Date#(YearMonth, 'YYYY-MMM'), Year(Today())) or

  AddMonths(SetDateYear([Start Date], Year(Today())), 3) = SetDateYear(Date#(YearMonth, 'YYYY-MMM'), Year(Today())) or

  AddMonths(SetDateYear([Start Date], Year(Today())), 6) = SetDateYear(Date#(YearMonth, 'YYYY-MMM'), Year(Today())) or

  AddMonths(SetDateYear([Start Date], Year(Today())), 9) = SetDateYear(Date#(YearMonth, 'YYYY-MMM'), Year(Today())), Sum(MonthlyCost)),

If(Frequency = 'Monthly', Sum(MonthlyCost))))


Capture.PNG

markramiro
Creator II
Creator II
Author

Hi Sunny, this is amazing!

So I noticed that if the Start Date is in the middle of the month e.g. January 20, the record does not get pulled to the pivot table. What I added to your expression is put a MonthStart on the [Start Date]. This resolved pulling records with partial months. My challenge now is that where the [End Date] is in the middle of the month (i.e. End Date is not the last day of the month) is that the last month gets calculated with a cost, but I don't want it to do so.

So for example:

Start Date, End Date, Frequency, Cost

1/20/2018, 7/19/2018, Monthly, 500

Right now it will show 500 on 2018-Jan, 2018-Feb, 2018-Mar, 2018-Apr, 2018-May, 2018-Jun, 2018-Jul. However I only need it to calculate until 2018-Jun. In other words if [End Date] is not the last day of the month, the pivot table should not calculate for that month. I'm currently struggling how to implement this.

Capture_1.PNG

Capture2.PNG

So in the above:

DEF should not reflect any cost for 2019-Jan. Last "payment" should be 2018-Oct.

GHI should not reflect any cost for 2018-Jul. Last "payment" should be 2018-Jun.

ABC should not reflect any cost for 2019-Jan. Last "payment" should be 2018-Jan.

sunny_talwar

May be try changing Calendar script to this

Calendar:

load Distinct MonthStart(tempDate) as %DateKey,

Floor(MonthStart(tempDate)) as DateNum,

Year(tempDate)&'-'&Month(tempDate) as YearMonth,

Year(tempDate) as CostsYear,

1 as MonthMultiplicator

resident tempDates;


LOAD recID,

Monthstart(AddMonths([Start Date],iterno()-1)) as %DateKey,

Cost as MonthlyCost

Resident PAYMENTS

While Monthstart(AddMonths([Start Date],iterno()-1)) <= If(Floor(MonthEnd([End Date])) = [End Date], Monthstart([End Date]), Monthstart([End Date], -1));

markramiro
Creator II
Creator II
Author

AMAZING!

markramiro
Creator II
Creator II
Author

Hi Sunny,

Hoping can get your insights on this even if I've already closed it. I expanded the data so that a record can have two Expense Type (Expense A, Expense B) and also can have multiple Frequencies. If need me to open a new thread, let me know.

I have observed that the pivot chart does not want to calculate for records that have multiple Frequencies unless a selection is made on [Frequency] or [Expense Type]. Been trying to modify the Expression since forever but haven't been successful.

  • ID=123 (has two rows, both are the same Expense Type and both are the same Frequency) - no issue. Pivot Chart correctly sums up the Costs.
  • ID=456 (has two rows, different Expense Types and both are the same Frequency) - no issue. Pivot Chart correctly reflects the data for Expense A and Expense B.
  • ID=678 (has two rows, both are the same Expense Type but one is Monthly and one is Quarterly) - does not pull to the chart unless user makes a Selection on [Frequency]. I am trying to make this work such that the cost of the two rows are being totaled. 2018-Jan would be 500 (300+200), 2018-Feb would be 300, 2018-Mar would be 300, 2018-April would be 500 (300+200)... etc. If user selects [Frequency], it partially works (see below, first image is if user selects "Monthly" and second image is if user selects "Quarterly"). The desired output is they should reflect at the same time (totaled) and without need for user selection.

Image_qvw_A.PNG

Image_qvw_B.PNG

  • ID=789 (has two rows, different Expense Types and one is Quarterly and one is Monthly) - does not pull to the chart unless user makes a Selection on both [ID] and any [Expense Type]. See image below which is the output when I selected ID=789 and selected any Expense Type. It is correct however I am trying to get the Pivot Chart to reflect this without user selection.

          Image_qvw.PNG

Thanks!

sunny_talwar

Check now

For 678

Capture.PNG

For 789

Capture.PNG