Number of months from today till date spread across Years

[qlikwiz123]

I already have calculated number of months from Today and Final Date.

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.

OUTPUT:

2020 - 1

2021 - 12

2022 - 12

2023 - 3

[edwin]

if final date does not change, you can do this via script, store the months and year in a separate table, then just count distinct months.  or you can count distinct months in your calendar by year where date is between today and final date

[qlikwiz123]

Hi,

I am not sure I follow. Every ID has a different Final Date and I am creating 'Year' column in the script using loops to generate the Year values from Today and the Final Date.

[edwin]

like this:

where the list shows dates between dec 2019 and May 2023
the first expression shows all the months by year, 2nd expression is 

=count(DISTINCT {<date={">=$(=today())<=3/23/2023"}>}Month)

 assuming arbitrary final date =3/23/2023

[qlikwiz123]

@edwin 

This is really helpful. But I don't have Month like you do. I only have 

ID, Final Date, Final Year

as my columns.

I am creating Year 

LOAD

min([FINAL YEAR]) as minYear,

max([FINAL YEAR]) as maxMax

Resident Main Where [FINAL YEAR]>=Year(Today());

YEAR:

noconcatenate

LOAD

minYear + Iterno()-1 As Year,
ID,

resident Main
While minYear + IterNo() -1 <= [FINAL YEAR] and [FINAL YEAR]>=Year(Today());

How do I make this Dynamic? Instead of entering the final date in the Set Expression, how do I use [Final Date] column in the same so that for each record, it is taking its own Final Date value?

[edwin]

do you not have a calendar with dates/Month/Year?  if you can create year, you should be able to create months or even the calendar.  instead of final year use today() and max of final date to create the calendar.  what you need is a way to relate the dates, count the distinct months per year