topic Re: Number of days between 2 dates in QlikView

Number of days between 2 dates

Tue, 17 Mar 2015 08:40:42 GMT

Hi.

I am very new in Qlikview, I would like some assistance calculate number of days between 2 dates and exclude nulls in that expression.

I did this on my expression in straight table with Supplier as my dimension

date#(GirGrvOrderDate-GirPrefDelDate) however its diving nulls on some values.. how can I remove nulls on expression

my goal is to trace how many days was my supplier late for delivery

Thanks

Deli(SA)

Re: Number of days between 2 dates

arulsettu — Tue, 17 Mar 2015 10:02:16 GMT

try this

Date(interval(GirGrvOrderDate-GirPrefDelDate))

Re: Number of days between 2 dates

hic — Tue, 17 Mar 2015 10:05:34 GMT

Just calculate

GirPrefDelDate - GirGrvOrderDate

The Date() and Interval() functions are formatting functions, and you don't need to format this number. The above expression will return an integer - the number of days - which is what you want.

HIC

Re: Number of days between 2 dates

Anonymous — Tue, 17 Mar 2015 10:15:38 GMT

Hi Deli ,

you can also use

Interval(date(GirGrvOrderDate)-date(GirPrefDelDate),'d') as Days

here you will get the difference in days

Thanks

BKC

Re: Number of days between 2 dates

hic — Tue, 17 Mar 2015 10:26:36 GMT

Just a small note:

The Interval() function and the Date() function are both formatting functions, which means that they don't change the numeric value of the field. Hence, it is totally unnecessary to nest one function inside the other. The following two will return identical results:

Interval(date(GirGrvOrderDate)-date(GirPrefDelDate),'d')

Interval(GirGrvOrderDate-GirPrefDelDate,'d')

HIC

Since the delivery date usually happens after the order date, I think that GirPrefDelDate-GirGrvOrderDate is better than GirGrvOrderDate-GirPrefDelDate...

Re: Number of days between 2 dates

Anonymous — Tue, 17 Mar 2015 10:32:00 GMT

Thank you Henric

Re: Number of days between 2 dates

pradnyat — Tue, 17 Mar 2015 10:36:15 GMT

Hi Deli,

If u want to calculate no. of working days, then use networkdays function.

networkdays ('2007-02-19', '2007-03-01') returns 9.

Use Networkdays(GirGrvOrderDate, GirPrefDelDate).

If you want to calculate overall days then just use, GirGrvOrderDate-GirPrefDelDate.

Hope this will help you.

Re: Number of days between 2 dates

Tue, 17 Mar 2015 10:38:39 GMT

Thanks Henric

GirPrefDelDate-GirGrvOrderDate does give me the integer I want, however there are nulls on other suppliers. how do I suppress nulls on expressions

Deli

Re: Number of days between 2 dates

Tue, 17 Mar 2015 10:45:51 GMT

This does work thanks

however how do I suppress nulls on expressions because im not interested in those. I need numbers only.

Deli

Re: Number of days between 2 dates

pradnyat — Tue, 17 Mar 2015 10:57:38 GMT

Check 'Supress when value is NULL' option for Suppliers dimension.

Re: Number of days between 2 dates

Tue, 17 Mar 2015 11:00:24 GMT

I tried that it displays null on my calculated expression

Re: Number of days between 2 dates

Anonymous — Tue, 17 Mar 2015 11:05:54 GMT

use calculated dimension in place of Supplier

if (GirPrefDelDate-GirGrvOrderDate >1,Supplier)

it will show only suppliers who have value in day columns

Thanks

BKC

Re: Number of days between 2 dates

Tue, 17 Mar 2015 11:31:56 GMT

When I use if (GirPrefDelDate-GirGrvOrderDate >1,Supplier) it returns no values on my straight table which is wrong however I tried

if(GirPrefDelDate-GirGrvOrderDate >'',Supplier) then it worked, does ' ' represent null in Qlikview?

Re: Number of days between 2 dates

pradnyat — Tue, 17 Mar 2015 11:38:12 GMT

Use calculated dimension:

if (not isnull(GirPrefDelDate) and not isnull(GirGrvOrderDate) ,Supplier)

Re: Number of days between 2 dates

hic — Tue, 17 Mar 2015 11:39:54 GMT

No, '' does not mean NULL. It means an empty string. Your comparison does not make a numeric comparison, but instead a string comparison.

The problem you have in your first expression, is that you have naked field references (no aggregation function), which will create problems. You could try

Concat(If(GirPrefDelDate-GirGrvOrderDate >1,Supplier), ',')

See more on Use Aggregation Functions!

HIC

Re: Number of days between 2 dates

Tue, 17 Mar 2015 12:20:02 GMT

if (not isnull(GirPrefDelDate) and not isnull(GirGrvOrderDate) ,Supplier) works perfectly.

Thanks everyone

Deli

Re: Number of days between 2 dates

Tue, 17 Mar 2015 13:02:42 GMT

Hi,

Try this expression.

NetWorkDays(Floor(Date(GirGrvOrderDate)),Floor(Date(GirPrefDelDate)))

NetWorkDays(Floor(Date(GirPrefDelDate)),Floor(Date(GirGrvOrderDate)))

Re: Number of days between 2 dates

Tue, 17 Mar 2015 13:18:57 GMT

These two expression worked for number of days between 2 dates 🙂 🙂

NetWorkDays(Floor(Date(GirPrefDelDate)),Floor(Date(GirGrvOrderDate)))
Networkdays( GirPrefDelDate,GirGrvOrderDate)

Thank you so much for your help guys..

Deli

Re: Number of days between 2 dates

pradnyat — Wed, 18 Mar 2015 05:46:31 GMT

Hi Deli,

Please mark the answer as correct to close this thread.

Re: Number of days between 2 dates

Wed, 18 Mar 2015 12:46:46 GMT

Hi Prad

How do i do that... . i have looking for the icon i cannot see it