Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join “Do More With Qlik” with Mike Tarallo to get a first-hand look at the New Authoring Experience, June 29th: REGISTER HERE
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)

20 Replies
Not applicable
Author

I tried  that it displays null on my calculated expression

balkumarchandel
Specialist II
Specialist II

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

Not applicable
Author

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?

pradnyat
Creator
Creator

Use calculated dimension:

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

Henric_Cronström

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

Not applicable
Author

Hi

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

Thanks everyone

Deli

Not applicable
Author

Hi,

Try this expression.

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

                                       OR

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

Not applicable
Author

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

  1. NetWorkDays(Floor(Date(GirPrefDelDate)),Floor(Date(GirGrvOrderDate)))
  2. Networkdays( GirPrefDelDate,GirGrvOrderDate)

Thank you so much for your help guys..

Deli

pradnyat
Creator
Creator

Hi Deli,

Please mark the answer as correct to close this thread.

Not applicable
Author

Hi Prad

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