topic How to Add Value (integer) to Date in QlikView

How to Add Value (integer) to Date

Sun, 28 Dec 2014 17:45:40 GMT

Hello,

I have Date in the following format 'D/MMM/YYYY' in a field called Date i want to add 20 years in value to it. Use this scenario

(Date, 'D/MMM/YYYY') + 20 years.

Just the addition of 20 more years in integer to the Date

Regards

Re: How to Add Value (integer) to Date

its_anandrjs — Sun, 28 Dec 2014 18:05:24 GMT

Hi,

Use Addmonths function add 20 years to the current field

Ex:-

=AddMonths( Date(Date#( '28/Dec/2014' ,'D/MMM/YYYY'),'D/MMM/YYYY'),240)

Or Add the New Field in the load script

Load

Date,

AddMonths(Date,240) as YearsAdd20

From Source;

Load

Date,

Date(AddMonths(Date,240),'D/MMM/YYYY') as YearsAdd20

From Source;

Regards

Anand

Re: How to Add Value (integer) to Date

Sun, 28 Dec 2014 18:25:36 GMT

Good Anand,

How do i subract a previous date with format 'D-MMM-YYYY' from today? To get the number of "Years, Month and Days" in this format example "2 years, 6 months and 2 days"

Any idea?

Re: How to Add Value (integer) to Date

its_anandrjs — Sun, 28 Dec 2014 21:02:36 GMT

Hi,

For this you have to convert the Today() date format to 'D-MMM-YYYY' and then add new field into load script for the calculation of the Years, Month and Days. Take difference of Year then month and then for days and concatenate all fields to get single field.

Source:
LOAD *,
     (Year(Date(Today(),'D-MMM-YYYY')) - Year(DATE))&' Years '&
     (Month(Date(Today(),'D-MMM-YYYY'))- Month(DATE)+(Year(Date(Today(),'D-MMM-YYYY'))-Year(DATE))*12)&' Months and '&
     Interval(Date(Today(),'D-MMM-YYYY') - DATE,'D')&' Days '   as DateDetails     
Resident tmpSource;

Drop Table tmpSource;

And you get field with

Hope this helps

Regards,

Anand

Re: How to Add Value (integer) to Date

Sun, 28 Dec 2014 22:11:32 GMT

Hello Anand,

No this isnt what i wanna achieve.

SCENARIO:

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

This is what i want to achieve

Re: How to Add Value (integer) to Date

Mon, 29 Dec 2014 00:19:24 GMT

Read this thread, its been solved already. I believe a VB function linked thru the Module is enough and you can call it from wherever you want in your document.

Re: How to Add Value (integer) to Date

Mon, 29 Dec 2014 07:24:15 GMT

Dear ,

Use Like This

floor((Today() -Date_of_Loss)/365) &' Years ' & floor(Frac((Today() -Date_of_Loss)/365)*12) &' Months '& frac(Frac((Today() -Date_of_Loss)/365)*12)*30 & ' Days' as Date,

08/06/2010

4 Years 4 Months 24 Days

Regards

Vimlesh Gupta

Re: How to Add Value (integer) to Date

Mon, 29 Dec 2014 07:33:49 GMT

Will this code work for a Leap-Year?

Re: How to Add Value (integer) to Date

Mon, 02 Feb 2015 10:41:01 GMT

Yes ,You can create Leap-year using it.