topic Re: Difference between two dates in Year,Month and Days in QlikView

Difference between two dates in Year,Month and Days

Anonymous — Wed, 02 Jan 2013 17:29:16 GMT

I have tried calculating difference between two dates example Qvw is attached it works for every date but only if the the end date's month is greater than the start. please see the image which is the problem.

Re: Difference between two dates in Year,Month and Days

Wed, 02 Jan 2013 17:44:08 GMT

Dear Irfan.

i think you need to use fab function when ever your geting - value

and in the Year field you need to use floor function to get the exact Year..

kindly find the attached qvw.

i hope it will help you.

thanks,

Mukram.

Re: Difference between two dates in Year,Month and Days

Anonymous — Wed, 02 Jan 2013 17:55:45 GMT

Dear Mukram,

Your answer is some what correct but i am not getting the desired result if your for id 6 its not getting the right value.

Re: Difference between two dates in Year,Month and Days

Gysbert_Wassenaar — Wed, 02 Jan 2013 18:31:01 GMT

Try these:

Months:

if(day([End Date])>=day([Start Date]),

num(month(MonthStart([End Date])-MonthStart([Start Date])))-1,

num(month(MonthStart([End Date])-MonthStart([Start Date])))-2)

Days:

if(day([End Date])>=day([Start Date]),

day([End Date])-day([Start Date]),

[End Date]-MakeDate(year([End Date]),month([End Date])-1,day([Start Date])))

Re: Difference between two dates in Year,Month and Days

Anonymous — Thu, 03 Jan 2013 05:32:43 GMT

As you can see above for id 6 and id 2 your month formula failes.

Re: Difference between two dates in Year,Month and Days

Anonymous — Mon, 07 Jan 2013 06:20:00 GMT

sorry i meant for id 5 and 2

Re: Difference between two dates in Year,Month and Days

Mon, 07 Jan 2013 08:42:28 GMT

How about this?

([End Date] <= AddYears([Start Date],1),'0 Years ',
Age([End Date],[Start Date]) & ' Years ')
&
IF(MONTH([End Date]) = MONTH([Start Date]),'0 Months ',
IF(DAY([Start Date]) < DAY([End Date]), fabs(MONTH([End Date])-Month([Start Date])) & ' Months ',
((MONTH([End Date])+12)-Month([Start Date]))-1 & ' Months '
))
&
IF(MONTH([End Date]) = MONTH([Start Date]),DAY([End Date]) - DAY([Start Date]) & ' Days',
IF(DAY([End Date]) > DAY([Start Date]),DAY([End Date]) - DAY([Start Date]) & ' Days',
DAY(DATE(Interval([End Date] - [Start Date])))-1 & ' Days'
))

Re: Re: Difference between two dates in Year,Month and Days

Fri, 10 Jan 2014 15:58:51 GMT

Thanks everyone for this. I needed to do exactly that. I made myself an example to make all this work and I would like to share it with you.

Complete fully working qvw and source code. Thanks to Haider Al-Seaidy

for his correct answer. It's exactly what I needed.

Eric Tremblay in Montreal, Canada

// Sample data

Data_test:
load
date(date1,'YYYY-MM-DD') As ORDER_DATE;
LOAD * INLINE [
date1
2011-01-15,
2011-02-18,
2008-01-15,
2010-02-18,
2007-01-15,
2009-02-18,
2012-01-15,
2014-02-18,
];

Temp:
LOAD
Min(date(ORDER_DATE,'YYYY-MM-DD')) as MinDate,
Max(date(ORDER_DATE,'YYYY-MM-DD')) as MaxDate
Resident Data_test;

LET vMinDate = floor(peek('MinDate'));
LET vMaxDate = floor(peek('MaxDate'));

// This section here will calculate
// the age between the smallest and the largest dates
min_max_order_dates:
load

// Original Code by Haider Al-Seaidy. Thanks!
IF (MaxDate <= AddYears(MinDate,1),'0 Years ',
Age(MaxDate,MinDate) & ' Years ')
&
IF(MONTH(MaxDate) = MONTH(MinDate),'0 Months ',
IF(DAY(MinDate) < DAY(MaxDate), fabs(MONTH(MaxDate)-Month(MinDate)) & ' Months ',
((MONTH(MaxDate)+12)-Month(MinDate))-1 & ' Months '
))
&
IF(MONTH(MaxDate) = MONTH(MinDate),DAY(MaxDate) - DAY(MinDate) & ' Days',
IF(DAY(MaxDate) > DAY(MinDate),DAY(MaxDate) - DAY(MinDate) & ' Days',
DAY(DATE(Interval(MaxDate - MinDate)))-1 & ' Days'
)) As AGE_BETWEEN_MinDate_MaxDate

Resident Temp; // From this table

Drop Table Temp;

Re: Difference between two dates in Year,Month and Days

nico_ilog — Mon, 13 Oct 2014 20:51:56 GMT

I think this is probably the simplest way...

=((Date1- Date2) / 365) * 12

"=num(((vStartDate - vEndDate) / 365) * 12,'##')"

- Given that you initially convert your Dates (For purposes of the Sum) into Number / integer format.

Lemme Know if it worked for you too?

Rgds,

Nico Oberholzer

Re: Difference between two dates in Year,Month and Days

eddysanchez — Thu, 27 Jul 2017 17:56:03 GMT

The "correct answer" of this thread is not correct.

I do the next:

=If(day([End Date])>=day([Start Date]),

div(((year([End Date])*12)+month([End Date])) - (((year([Start Date])*12)+month([Start Date]))),12) & ' Years'

&' '&

mod(((year([End Date])*12)+month([End Date])) - (((year([Start Date])*12)+month([Start Date]))),12) & ' Months'

&' '&

([End Date]-AddMonths([Start Date],((year([End Date])*12)+month([End Date])) - (((year([Start Date])*12)+month([Start Date]))))) & ' Days'

div(((year([End Date])*12)+month([End Date])) - (((year([Start Date])*12)+month([Start Date])))-1,12) & ' Years'

&' '&

mod(((year([End Date])*12)+month([End Date])) - (((year([Start Date])*12)+month([Start Date])))-1,12) & ' Months'

&' '&

([End Date]-AddMonths([Start Date],((year([End Date])*12)+month([End Date])) - (((year([Start Date])*12)+month([Start Date])))-1)) & ' Days'

)

Re: Difference between two dates in Year,Month and Days

auzzie4185 — Tue, 22 Jan 2019 20:08:39 GMT

I agree that the correct answer is incorrect. I noticed it because it gave me 0 years 0 months 0 days when it was exactly 1 year. I was looking at ways to correct it when I scrolled down and found yours!!!

Thank you!

Auzzie

Re: Difference between two dates in Year,Month and Days

SiO2y — Tue, 24 Mar 2020 10:51:57 GMT

If you have the opportunity to calculate in the load script, a preceding load makes the script more manageable - and easier to correctly add 's' for multiple years/months/days etc.

This script will only show a value for years, months or days if there is one (e.g. if the duration is exactly 2 years and 1 day, it will output "2 years, 1 day" rather than "2 years, 0 months, 1 days")

Examples:

0 years, 0 months, 11 days: "11 days"
0 years, 2 months, 1 day: "2 months, 1 day"
1 year, 3 months, 0 days: "1 year, 3 months"
0 years, 1 month, 0 days: "1 month"
1 year, 2 months, 3 days: "1 year, 2 months, 3 days"

LOAD If([_y] = 0, '', [_y] & If([_y] = 1, ' year' , ' years' ) & If([_m] > 0 or [_d] > 0, ', ', '')) & If([_m] = 0, '', [_m] & If([_m] = 1, ' month', ' months') & If([_d] > 0, ', ', '')) & If([_d] = 0, '', [_d] & If([_d] = 1, ' day' , ' days' )) as [Duration]; LOAD If(Day([Support to]) >= Day([Support from]), Div(((Year([Support to]) * 12) + Month([Support to])) - (((Year([Support from]) * 12) + Month([Support from]))) , 12), Div(((Year([Support to]) * 12) + Month([Support to])) - (((Year([Support from]) * 12) + Month([Support from]))) - 1, 12)) as [_y] , If(Day([Support to]) >= Day([Support from]), Mod(((Year([Support to]) * 12) + Month([Support to])) - (((Year([Support from]) * 12) + Month([Support from]))) , 12), Mod(((Year([Support to]) * 12) + Month([Support to])) - (((Year([Support from]) * 12) + Month([Support from]))) - 1, 12)) as [_m] , If(Day([Support to]) >= Day([Support from]), ([Support to] - AddMonths([Support from], ((Year([Support to]) * 12) + Month([Support to])) - (((Year([Support from]) * 12) + Month([Support from]))))), ([Support to] - AddMonths([Support from], ((Year([Support to]) * 12) + Month([Support to])) - (((Year([Support from]) * 12) + Month([Support from]))) - 1))) as [_d]