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: 
lbrosten
Contributor III
Contributor III

Calculate the number of months between two dates

I have 4 dates and I need to be able to calculate the number of months between each. For example: between loss date and report date but also between loss date and closed date. What expression do I use and do I need to create multiple master items? What if one of these dates is null?

Loss Date, Report Date, First Reserve Date, Closed Date

Case No Loss Date Report Date First Reserve Date Closed Date
100 May-18 Aug-19 Jan-20 Sep-21
101 Jun-16 Apr-18 Jul-18 Jun-20
102 Jul-21 Sep-21   Jan-22
1 Solution

Accepted Solutions
albertovarela
Partner - Specialist
Partner - Specialist

The methods I suggest will provide the number of days

1) Sum([Report Date]-[Loss Date]) 

2) NetWorkDays([Report Date],[Loss Date])

but you require number of months so you need to divide by "how many days per month" .  On the solution, @nevopotokcloudinary  suggests to divide by 30.  That denominator is the business rule I was referring to. 

View solution in original post

5 Replies
albertovarela
Partner - Specialist
Partner - Specialist

I guess you could do a simple subtraction of dates e.g. Sum([Report Date]-[Loss Date]) or perhaps find the net working days in between e.g. NetWorkDays([Report Date],[Loss Date]) . To convert to months you need to come up with a business and define the proper denominator.

I hope this helps

nevopotokcloudinary
Contributor III
Contributor III

You can use this expression for all months intervals (absolute intervals)

Fabs(Div(LossDate-ReportDate,30)+1)

 

nevopotokcloudinary_1-1644610073787.png

 

lbrosten
Contributor III
Contributor III
Author

Thanks. Can you tell me more about coming up with a business and define the proper denominator. Not sure what that means.

lbrosten
Contributor III
Contributor III
Author

Thanks. What if I want to see month intervals with % of month. Example using may 1 and June 15, rather than 1 month to show 1.5 months.

albertovarela
Partner - Specialist
Partner - Specialist

The methods I suggest will provide the number of days

1) Sum([Report Date]-[Loss Date]) 

2) NetWorkDays([Report Date],[Loss Date])

but you require number of months so you need to divide by "how many days per month" .  On the solution, @nevopotokcloudinary  suggests to divide by 30.  That denominator is the business rule I was referring to.