Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Number of days between 2 dates

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)

1 Solution

Accepted Solutions
pradnyat
Creator
Creator

Use calculated dimension:

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

View solution in original post

20 Replies
arulsettu
Master III
Master III

try this

Date(interval(GirGrvOrderDate-GirPrefDelDate))

Henric_Cronström

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

balkumarchandel
Specialist II
Specialist II

Hi Deli ,

you can also use

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

here you will get the difference in days

Thanks

BKC

Henric_Cronström

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

PS

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

balkumarchandel
Specialist II
Specialist II

Thank you Henric

pradnyat
Creator
Creator

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.

Not applicable
Author

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

Not applicable
Author

This does work  thanks

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

Deli

pradnyat
Creator
Creator

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