Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Calculate number of days between two date fields?

Hi,

I have two dates in my table, Shipped Date and order Date, I want to calculate the number of Days between these two tables coming from the table. if I hardcode the value correctly I am able to get the value but if I am putting field Name. I am not getting and it giving some random value .I have seen many posts on this but couldn't able to check with the right post.

1 Solution

Accepted Solutions
maxgro
MVP
MVP

usually is just the difference

[Shipped Date] - [Order Date]

if you still have problem, try to post a small example (.qvw) of your problem

View solution in original post

12 Replies
maxgro
MVP
MVP

usually is just the difference

[Shipped Date] - [Order Date]

if you still have problem, try to post a small example (.qvw) of your problem

Colin-Albert

As Massimo says it should just be the difference between the two fields.

Can you check that the data in the two fields is loaded correctly as a date field.

If you put each date field in a list box, are the dates left or right justified?

If the dates are correctly loaded as date fields (dual values), the dates should show as right justified in your list box.

Have a look at this post for more help on dates & formatting Get the Dates Right

Anonymous
Not applicable
Author

you could also use num([Shipped Date]) - num([Order Date]) if your date formats are in different

MarcoWedel

sounds like one of your date fields is not dual (i.e. it has no numerical value, just text).

check in the table viewer or with the num(yourdatefield) function.

regards

Marco

Not applicable
Author

Hi All,

Thanks for your replies !!!..

I went and checked in the Edit transformation Step and it showing some numbers like 8897.00 instead of Date mentioned in the Excel file. Hope there is some mistake while loading.?

avinashelite

yes, convert that to proper date  format ...

like date(field,'MM-DD-YYYY') as field

and you can use interval function to get the number of days

eg:

interval( A-B, 'D hh:mm' ) where A=97-08-06 09:00:00 and B=96-08-06 00:00:00 returns:

   

String

365 09:00

Number

365.375 

jagan
Luminary Alumni
Luminary Alumni

Hi,

While load data from Excel convert your date fields into date format using Date() like below

LOAD

Date(DateField1) AS DateField1,

Date(DateField2) AS DateField2,

Num(Date(DateField2) - Date(DateField1)) AS DaysDiff

'

'

'

FROM FileName.xls;

Hope it helps you.

Regards,

Jagan.

Not applicable
Author

Got the output Thanks for all your suggestions !!!

avinashelite

Hi Sreeharsha,

Cool you got the answer, can you please mark the correct answer and close this thread .