Number of months from today till date spread across Years

[qlikwiz123]

From today in 2020 to 2023 March, there are 28 months. I am able to show the number of months from today and the final date but not able to split it between years. For example, 2020 has only one month left. So it should be 1. In 2023, since final date is 03/31/2023, NoOfMonths should be 3.

2020 - 1

2021 - 12

2022 - 12

2023 - 3

[Vegar]

You are not telling us how you are calculating in order to get 28 for all years, but you could take a look at this old post to check if it will be of help: https://community.qlik.com/t5/QlikView-Documents/Calculating-Months-difference-between-two-dates/ta-... . It gives an example on how to calculate no of months between two dates. 

[qlikwiz123]

Hi,

I already have this  to calculate the number of months from Today and the Final Date. But as you can see, it is showing the same number of months (28) for all the years while it is not true (2020 only has one month left and 2023 only has 3 months since the Final Date is 03/31/2023). I want to distribute these 28 months into the respective years.

I have my Data and expected output in my question too.

[mfchmielowski]

I think that You need to have more information columns. Not only year.

My sample below, without commets but it's reable 😉

let finalDate = date('2023-03-31', 'YYYY-MM-DD');

Table:
load
	*
	, if(year(dateYearStart) <> year('$(finalDate)')
		, if(year(date) = year(today(1))
			, (year(dateYearEnd) * 12 + num(month(dateYearEnd))) -  (year(dateMonthStart) * 12 + num(month(dateMonthStart))) + 1
			, (year(dateYearEnd) * 12 + num(month(dateYearEnd))) -  (year(dateYearStart) * 12 + num(month(dateYearStart))) + 1
		)
		, (2023*12 + 3) - (year(dateYearStart) * 12 + num(month(dateYearStart))) + 1
	) as monthsTillEndYearOrFinal
;
load *
	, monthStart(date) as dateMonthStart
	, yearStart(date) as dateYearStart
	, YearEnd(date) as dateYearEnd
	, year(date) as dateYear
inline [
	date
	2020-12-30
	2021-01-01
	2021-02-01
	2022-01-01
	2022-12-01
	2023-01-01
	2023-02-01
	2023-02-01
];

Result table looks like this:

[qlikwiz123]

@mfchmielowski  Is it possible to attach the QVW please?

[mfchmielowski]

I've attached almost full load script before but here you go.

[qlikwiz123]

I see that you are manually creating dates for every month. 

I only have 

ID, Final Date as my columns where I am generating Year(Final Date) to create Year field.

How do I count months in this case and also make the Final Date Dynamic instead of hard quoting it in Variable?

[mfchmielowski]

Yes, i've created dates table manualy 🙂 i don't have your input table.
You can always generate calendar table since today() to some final date and adapt code to it. The variable value can be peek'ed from table.

I've upgraded code and attached.