Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
apthansh
Creator
Creator

No. of months between 2 dates and No. of Months within Year.

How can I get the number of months between 2 dates including lookinf fo no. of months between 2 years.

Start Date - 10/1/2019

End Date -12/1/2020

Month difference - I am using below - 14 months diff

floor((date([End Date],'DD/MM/YYYY') - date([Start Date],'DD/MM/YYYY'))/30)

now out of 14 months - I want to see how many months in start Date and How many months in End Date.

Can I get to this detail ?

Thank you much.

 

 

Labels (7)
5 Replies
Taoufiq_Zarra

Hi,

can you give one other example ?

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
Rodj
Luminary Alumni
Luminary Alumni

floor(YearEnd((date([Start Date],'DD/MM/YYYY')) - date([Start Date],'DD/MM/YYYY'))/30) will give you the months in the start dates year. Similarly you can use the YearStart function to calculate the months in the end date  year. Is that what you are after?

Cheers,

Rod

apthansh
Creator
Creator
Author

Here is another detailed example that I am trying to achieve.

Data is as below.

IDStart DateEnd DateSavings
AAA7/1/20191/31/20214,000,000

 

For AAA No. of months b/w start date and end date is 19 Months, out of 19 months 6 Months is in 2019, 12 Months is in 2020, 1 month is in 2021.

I want the savings to be distributed across all these years.so per month savings would be 4,000,000/19 = $ 210,526.
 

 2019 Savings would be 210,526 * 6 = $1,263,157
2020 Savings would be 210,526 *  12 = $2,526,312
2021 Savings would be 210,526 *  1 = $210,526

 

Bar chart will have 3 years - 2019, 2020, 2021 with the respective savings.

 

 

 

 

Rodj
Luminary Alumni
Luminary Alumni

How do these duplicate threads happen? Fortunately I'm in a very different timezone and hadn't wasted more time putting together a solution now that we finally had the actual requirement!