Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

count days between dates

Hi, in my expressions, I had:

today() - ArrivalDate and the result was 383.6754...

So I did: Num(Date(ArrivalDate, 'DD-MM-YYYY')) to see exactly what number I get.  I got 41466.324....

I want both of my dates to be integers but I don't know whether to round them up or down.  I don't think rounding is the answer otherwise, some days, I could get 1 day out from the rounding.

Any ideas for novice me, Qliksters?

1 Solution

Accepted Solutions
MK_QSL
MVP
MVP

Interval(today() - ArrivalDate,'dd')

or

Today() - Floor(ArrivalDate)

View solution in original post

9 Replies
er_mohit
Master II
Master II

try to use floor function

MK_QSL
MVP
MVP

Interval(today() - ArrivalDate,'dd')

or

Today() - Floor(ArrivalDate)

View solution in original post

Not applicable
Author

Sounds like your ArrivalDate field is actually a date and timestamp, which is why it is giving you the decimal.

If you compare

Num(today())

with

Num(now())

for instance you will see the difference that causes.

Mohit is right though, if you floor the ArrivalDate and then do your calculation, that should give you the answer

hope that helps

Joe

Not applicable
Author

Make sure that the arrival date has the same format that today() has. Today has no hours or minutes and ArrivalDate seems to have them.

Not applicable
Author

Thanks Manish and Mohit,


Arrival date = 12/08/2013:

For Interval(today() - ArrivalDate,'dd'), I get 351. 


For Floor(Today()) - Floor(ArrivalDate), I get 352.


Using Sql:  I get 1.6755 if it's the difference between now and yesterday.  Or 1 if I have:

select sysdate - to_date('29/07/2014', 'DD/MM/YY').  And using the Arrival date, I get 352.6788, so Floor must be right.

But if I use Interval(today() - <yesterday>, 'dd'), I get '01'  <-- I would've thought I would get 0 since the above example showed Interval is one less than Floor.


I'll read up on Floor and Interval but I think Floor is right - basically, I want to ignore the time and just get the number of days.

Not applicable
Author

Thanks Joe Simmons,

Won't I need to Floor both today and the ArrivalDate?

It sounds like as if Floor strips it of the timestamp - which is what I'm after.  I don't care what time the Arrival time is - as long as it's before 12 midnight, it's considered yesterday.

Not applicable
Author

Thanks Jose,

You're right - I didn't realise that today() has no hours nor minutes.

It makes sense then to only floor the ArrivalDate.

Not applicable
Author

No worries glad to be of help, and no (as you've seen from Jose's post) you won't need it for the today() as that is already a date only with no timestamp 

AT84Qlik
Contributor II
Contributor II

Thanks - needed this for my own dashboard so was a lifesaver!