topic Re: Expression/ Difference between Date in QlikView

Expression/ Difference between Date

Sun, 12 Jan 2014 19:34:49 GMT

Hi! How can I calculate the number of days between two dates? For example from 16.04.2013 to 20.08.2013?

Any suggestions greatly appreciated.

Andrea

Re: Expression/ Difference between Date

maxgro — Sun, 12 Jan 2014 20:08:29 GMT

Hi Andrea

= date#('20.08.2013', 'DD.MM.YYYY') - date#('16.04.2013', 'DD.MM.YYYY')

Re: Expression/ Difference between Date

Sokkorn — Mon, 13 Jan 2014 01:32:05 GMT

Hi Massimo,

If your data is a Date field, then simply use =[Date2]-[Date1]

If your data is a Text field (ex. 20.08.2013), then you need to format it first before calculation

=Date#([Date2],'dd.MM.yyyy')-Date#([Date1],'dd.MM.yyyy')

You may use Interval as well.

=Interval([Date2]-[Date1],'D hh:mm')

=Interval(Date#([Date2],'dd.MM.yyyy')-Date#([Date1],'dd.MM.yyyy'),'D hh:mm')

Regards,

Sokkorn

Re: Expression/ Difference between Date

jagan — Mon, 13 Jan 2014 03:44:16 GMT

Hi,

Try like this

=Interval(Date#([ToDate], 'DD.MM.YYYY')-Date#([FromDate],'DD.MM.YYYY'),'D')

Regards,

Jagan.

Re: Expression/ Difference between Date

Wed, 15 Jan 2014 20:28:52 GMT

Thank you for your help

Re: Re: Expression/ Difference between Date

Wed, 15 Jan 2014 20:43:28 GMT

Hi! If I have 16.04.2013 as value in "start date" field and 28.02.2014 as value in "end date", and I only consider the interval from 01.01.2013 to 31.12.2013, how can I write my expression?

Thank you for any help you can provide in this situation.

Andrea

Re: Expression/ Difference between Date

Sokkorn — Thu, 16 Jan 2014 01:28:30 GMT

Hi Andrea,

Use this in text object:

=YearStart(Today()) -> Return 01-01-2014

=YearEnd(Today()) -> Return 31-12-2014

=YearStart(AddYears(Today(),-1)) -> Return 01-01-2013

=YearEnd(AddYears(Today(),-1)) -> Return 31-12-2013

Hence, we can use interval

=Interval(YearEnd(AddYears(Today(),-1))-YearStart(AddYears(Today(),-1)),'D') -> Return 364

Regards,

Sokkorn

Re: Re: Expression/ Difference between Date

jagan — Thu, 16 Jan 2014 01:41:31 GMT

HI,

Try like this

=Interval(YearStart(Date#([ToDate], 'DD.MM.YYYY'))-YearEnd(Date#([ToDate],'DD.MM.YYYY')),'D')

Hope this helps you.

Regards,

Jagan.

Re: Expression/ Difference between Date

nizamsha — Thu, 16 Jan 2014 06:10:23 GMT

TableA:

LOAD * INLINE [

Date,Date2

01.01.2012,01.01.2013

01.02.2012,01.03.2012

];

LOAD Date as Date1,

Date2 as Date3,

Date2-Date as Date5,

Date#( Date2 ,'DD.MM.YYYY')-Date#( Date ,'DD.MM.YYYY')as Date6

Resident TableA;

Re: Expression/ Difference between Date

nizamsha — Thu, 16 Jan 2014 06:12:12 GMT

TableA:

LOAD * INLINE [

Date,Date2

01.01.2012,01.01.2013

01.02.2012,01.03.2012

];

LOAD Date as Date1,

Date2 as Date3,

Interval#( Date#( Date2 ,'DD.MM.YYYY')-Date#( Date ,'DD.MM.YYYY'),'D') as Date6

Resident TableA;

Re: Expression/ Difference between Date

Thu, 16 Jan 2014 06:12:19 GMT

Hi Andrea

please check the following. its working

Date# ('20-08-2013,'DD-MM-YYYY')-Date#('16-04-2013','DD-MM-YYYY')

regards

harshal

Re: Expression/ Difference between Date

Thu, 16 Jan 2014 14:29:10 GMT

Hi! How can I write the expression when I want to consider only this interval:

Option A. From 01.06.2013 to 30.09.2013;

Option B. From 01.10.2013 to 28.02.2014;
Thank you for any help you can provide.

Andrea

Re: Expression/ Difference between Date

Thu, 16 Jan 2014 14:29:54 GMT

Thank you for your help!

Re: Re: Re: Expression/ Difference between Date

Thu, 16 Jan 2014 19:52:20 GMT

Can you help me with an example pls?

You can find an example attached.

I really appreciate any help you can provide.

Andrea

Re: Expression/ Difference between Date

Clever_Anjos — Thu, 16 Jan 2014 19:56:48 GMT

floor(d2) - floor(d1) number of days between d2 and d1

Re: Re: Re: Re: Expression/ Difference between Date

preminqlik — Thu, 16 Jan 2014 20:26:06 GMT

hi andrea

find attachment

Re: Re: Re: Re: Expression/ Difference between Date

Thu, 16 Jan 2014 21:38:13 GMT

Hi! Thank you for your help!

I'd like to find a way to calculate the number of days between two dates only considering this interval:

Example 1: from 01.01.2013 to 31.12.2013;

Example 2: From 01.06.2013 to 30.09.2013;

Example 3: From 01.10.2012 to 28.02.2013;
Thank you for any help you can provide.

Andrea

Re: Expression/ Difference between Date

jagan — Fri, 17 Jan 2014 02:15:58 GMT

Hi,

Try this script

Data:

LOAD

IF(([Start Date] = Date('01/01/2013') AND [End Date] = Date('31/12/2013')) OR ([Start Date] = Date('01/06/2013') AND [End Date] = Date('30/09/2013')) OR

([Start Date] = Date('01/10/2012') AND [End Date] = Date('28/02/2013')), Interval([End Date] - [Start Date], 'D')) AS DateDiff;

LOAD [N. Company],

Company,

Employee,

[Start Date],

[End Date]

FROM

(ooxml, embedded labels);

Regards,

Jagan.

Re: Re: Re: Re: Expression/ Difference between Date

preminqlik — Fri, 17 Jan 2014 05:29:38 GMT

for eg :

Example 1: from 01.01.2013 to 31.12.2013;

if((num(date#('31.12.2013','DD.MM.YYYY')))-(num(date#('01.01.2013','DD.MM.YYYY')))>0,

(num(date#('31.12.2013','DD.MM.YYYY')))-(num(date#('01.01.2013','DD.MM.YYYY'))),0)

Re: Expression/ Difference between Date

Fri, 17 Jan 2014 05:54:13 GMT

Date# ('20-08-2013,'DD-MM-YYYY')-Date#('16-04-2013','DD-MM-YYYY')

regards

Datta