Qlik Community

QlikView Deployment

Discussion Board for collaboration related to QlikView Deployment.

Not applicable

day month year shows two dates qlikview

Hello:

I need to get the time work by an employee. In years, months, and days.

having a start date and end date.

use the following code and it did not work.

examplo:

end date:18/04/2014

start date: 15/06/2010

result:

years:3

months:10

days:3

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

helpme

please

rolando

1 Solution

Accepted Solutions
MVP
MVP

Re: day month year shows two dates qlikview

Maybe like this:

Set DateFormat = 'DD/MM/YYYY';

LOAD *,


Age([End Date],[Start Date]) & ' Years '

&

IF(month([Start Date]) < month([End Date]),

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

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

&

if(Day([End Date])> Day([Start Date]), Day([End Date])-Day([Start Date]) & ' Days', day(monthend([Start Date]))-day([Start Date])+day([End Date]) & ' Days') as Diff;


LOAD * INLINE [

End Date, Start Date

18/04/2014, 15/06/2010

18/06/2014, 15/06/2010

18/04/2014, 15/02/2010

8/06/2014, 15/06/2010

31/12/2014, 01/01/2013

01/01/2014, 31/12/2013

];

6 Replies
MVP
MVP

Re: day month year shows two dates qlikview

Maybe like this:

Set DateFormat = 'DD/MM/YYYY';

LOAD *,


Age([End Date],[Start Date]) & ' Years '

&

IF(month([Start Date]) < month([End Date]),

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

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

&

if(Day([End Date])> Day([Start Date]), Day([End Date])-Day([Start Date]) & ' Days', day(monthend([Start Date]))-day([Start Date])+day([End Date]) & ' Days') as Diff;


LOAD * INLINE [

End Date, Start Date

18/04/2014, 15/06/2010

18/06/2014, 15/06/2010

18/04/2014, 15/02/2010

8/06/2014, 15/06/2010

31/12/2014, 01/01/2013

01/01/2014, 31/12/2013

];

Not applicable

Re: day month year shows two dates qlikview


thank you

MVP
MVP

Re: day month year shows two dates qlikview

There is small issue in the Days calculation, where we need to use a larger equal comparison in the if clause:

LOAD *,


Age([End Date],[Start Date]) & ' Years '

&

IF(month([Start Date]) < month([End Date]),

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

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

&

if(Day([End Date])>= Day([Start Date]), Day([End Date])-Day([Start Date]) & ' Days', day(monthend([Start Date]))-day([Start Date])+day([End Date]) & ' Days') as Diff;


Not applicable

Re: day month year shows two dates qlikview

Hello:

I need to extract the exact number of years between two date.

for Examplo,

If startdate = 30/04/2006

And

enddate = 15/04/2014

Result to obtain is 7 years and 11 months

To put a range for 1 to 7 years that person should not leave me.

Because it has more than 7 years

How do to limit that option and just go down the 7

helpme

please

MVP
MVP

Re: day month year shows two dates qlikview

Maybe using the age() function:

=age('15/04/2014','30/04/2006')

Not applicable

Re: day month year shows two dates qlikview

The function age does not give me the exact years,

examplo:

=age('15/04/2014','30/04/2006')

We obtain: 7 years  with several months.

I need a function that I have fulfilled the seven years:

For examplo:

Of 1 to 7 years this age('15/04/2014','30/04/2006') not qualify,he have more time.

This Qualify age('15/04/2013','30/04/2006') 6 years with several months.

I need an exact function of completed years, not more

help me

Community Browser