Number of days between 2 dates

[#1]

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)

[#2]

I tried  that it displays null on my calculated expression

[Anonymous]

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

[#4]

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 · Accepted solution]

Use calculated dimension:

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

[hic]

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

[#7]

Hi

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

Thanks everyone

Deli

[#8]

Hi,

Try this expression.

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

                                       OR

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

[#9]

  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]

Hi Deli,

Please mark the answer as correct to close this thread.

[#11]

Hi Prad

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