Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

m_irfan360
Contributor

Difference between two dates in Year,Month and Days

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.

1 Solution

Accepted Solutions
Not applicable

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

How about this?

IF

([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'
))

9 Replies
Not applicable

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

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.

m_irfan360
Contributor

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

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

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])))


talk is cheap, supply exceeds demand
m_irfan360
Contributor

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

test2.jpg

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

m_irfan360
Contributor

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

sorry i meant for id 5 and 2

Not applicable

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

How about this?

IF

([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'
))

Not applicable

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

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;

nico_ilog
Contributor II

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

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

eddysanchez
Contributor

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

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'

)

Community Browser