topic Re: Number of months from today till date spread across Years in QlikView

Number of months from today till date spread across Years

qlikwiz123 — Sat, 16 Nov 2024 17:39:53 GMT

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

Re: Number of months from today till date spread across Years

Vegar — Wed, 30 Dec 2020 08:24:49 GMT

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-p/1486518 . It gives an example on how to calculate no of months between two dates.

Re: Number of months from today till date spread across Years

qlikwiz123 — Wed, 30 Dec 2020 13:37:58 GMT

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.

Re: Number of months from today till date spread across Years

mfchmielowski — Wed, 30 Dec 2020 14:31:25 GMT

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:

Re: Number of months from today till date spread across Years

qlikwiz123 — Thu, 31 Dec 2020 15:48:02 GMT

@mfchmielowski Is it possible to attach the QVW please?

Re: Number of months from today till date spread across Years

mfchmielowski — Thu, 31 Dec 2020 16:56:05 GMT

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

Re: Number of months from today till date spread across Years

qlikwiz123 — Thu, 31 Dec 2020 17:54:41 GMT

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?

Re: Number of months from today till date spread across Years

mfchmielowski — Thu, 31 Dec 2020 19:53:02 GMT

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.