topic Re: count days between dates in QlikView

count days between dates

Wed, 30 Jul 2014 07:54:31 GMT

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?

Re: count days between dates

er_mohit — Wed, 30 Jul 2014 07:55:27 GMT

try to use floor function

Re: count days between dates

MK_QSL — Wed, 30 Jul 2014 07:56:37 GMT

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

Today() - Floor(ArrivalDate)

Re: count days between dates

Wed, 30 Jul 2014 08:03:50 GMT

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

Re: count days between dates

Wed, 30 Jul 2014 08:16:31 GMT

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.

Re: count days between dates

Wed, 30 Jul 2014 08:23:36 GMT

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.

Re: count days between dates

Wed, 30 Jul 2014 08:28:01 GMT

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.

Re: count days between dates

Wed, 30 Jul 2014 08:32:10 GMT

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.

Re: count days between dates

Wed, 30 Jul 2014 09:09:11 GMT

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

Re: count days between dates

AT84Qlik — Wed, 09 Jan 2019 17:12:14 GMT

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