topic Re: Subtracting dates; Today() from previous date in QlikView

Subtracting dates; Today() from previous date

Mon, 29 Dec 2014 07:08:47 GMT

Hello,

How do i subtract Today from a previous date format 'D/MMM/YYYY' so i get my answer in the underline format below

SCENARIO:

Today() - December 1st 2000 = "14 years, and 27 days"

Rgds

Re: Subtracting dates; Today() from previous date

jagan — Mon, 29 Dec 2014 07:16:35 GMT

Hi,

Try like this

=If(Interval(Today() - MakeDate(2000,12,1), 'DD') >= 365,

Div(Num(Interval(Today() - MakeDate(2000,12,1), 'DD')), 365) & ' Years, ', '')

If(Mod(Interval(Today() - MakeDate(2000,12,1), 'DD'), 365) > 0,

Mod(Interval(Today() - MakeDate(2000,12,1), 'DD'), 365) & ' Days', '')

Regards,

Jagan.

Re: Subtracting dates; Today() from previous date

Mon, 29 Dec 2014 07:28:30 GMT

What i have there is just a senario, It can also return number of Year, Month and Day. Its too static

I mean somthing like this:

Today - "D/MMM/YYYY" = Number of Year, Number of Month and Number of Days

Re: Subtracting dates; Today() from previous date

jagan — Mon, 29 Dec 2014 08:09:57 GMT

Hi,

Try like this

=Age(Today(), MakeDate(1999,11,30)) & ' Years, ' & Mod(Num(((year(Today()) * 12) + month(Today())) - (((year(MakeDate(1999,11,30)) * 12) + month(MakeDate(1999,11,30))))), 12) & ' Months, '

& Fabs(Day(Today()) - Day(MakeDate(1999,11,30))) & ' Days'

Regards,

Jagan.

Re: Subtracting dates; Today() from previous date

Mon, 29 Dec 2014 08:42:09 GMT

Hello,

Please use this, the previous code didnt work. This will be more clearer.

Note: Use Employ Date

Thanks

Re: Subtracting dates; Today() from previous date

Anonymous — Mon, 29 Dec 2014 09:10:09 GMT

Hi Try the below code

=If(Today()-Date(MakeDate(2000,12,1),'DD/MM/YYYY') >=365,Round(Num(Today()-Date(MakeDate(2000,12,1),'DD/MM/YYYY')) / 365) & ' Years ' &
If(Today()-Date(MakeDate(2000,12,1),'DD/MM/YYYY') >=365,Date(Today()-MakeDate(2000,12,1),'DD')) & ' Days')

Re: Subtracting dates; Today() from previous date

Mon, 29 Dec 2014 09:14:27 GMT

Hello V

Find attached plz

Re: Subtracting dates; Today() from previous date

jagan — Tue, 30 Dec 2014 06:21:28 GMT

Hi,

Try this script

TableName:

LOAD
*,
Age(Today(), Date_Employed) & ' Years, ' & Mod(Num(((year(Today()) * 12) + month(Today())) - (((year(Date_Employed) * 12) + month(Date_Employed)))), 12) & ' Months, '
& Fabs(Day(Today()) - Day(Date_Employed)) & ' Days' AS Formatted_Date;
LOAD [S/N],
[Date of birth],
date([Date Employed]) as Date_Employed,
Sex
FROM

(qvd);

Now use Only(Date_Employed) AS Expression in your chart.

Regards,

jagan.