count days between dates

[#1]

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?

[er_mohit]

try to use floor function

[MK_QSL · Accepted solution]

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

or

Today() - Floor(ArrivalDate)

[#4]

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

[#5]

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.

[#6]

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.

[#7]

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.

[#8]

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.

[#9]

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]

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