Price per Month aggregated by Year

[qlikwiz123]

I have Price, Today() as Start Date and 10/25/2025 as EndDate

How do I generate months between Today() and EndDate, Add Price Per Month to each Month and show them in Years?

As an example

Current:

Price	ID	StartDate	EndDate
50	1	3/31/2020	2/25/2021
30	2	3/31/2020	5/14/2020

Result:

Price	ID	Year	Month	Price Per Month
30	2	2020	Mar	10
30	2	2020	Apr	10
30	2	2020	May	10
50	1	2020	Jan	0
50	1	2020	Feb	0
50	1	2020	Mar	4.16
50	1	2020	Apr	4.16
50	1	2020	May	4.16
50	1	2020	June	4.16
50	1	2020	Jul	4.16
50	1	2020	Aug	4.16
50	1	2020	Sep	4.16
50	1	2020	Oct	4.16
50	1	2020	Nov	4.16
50	1	2020	Dec	4.16
50	1	2021	Jan	4.16
50	1	2021	Feb	4.16

[lironbaram · Accepted solution]

hi 

this script will do the trick 

dataTemp:
load * inline [
Price,ID,StartDate,EndDate
50, 1, 3/31/2020, 2/25/2021
30, 2, 3/31/2020, 5/14/2020
];

data:
load *,
Price/(round((MonthStart(EndDate)- MonthStart(StartDate))/30)+1) as PricePerMonth,
date(AddMonths(MonthStart(StartDate),IterNo()-1)) as Month
Resident dataTemp
while date(AddMonths(MonthStart(StartDate),IterNo()-1))<=MonthStart(EndDate);

drop table dataTemp;