Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Live chat with experts, bring your API Integration questions. June 15th, 10 AM ET. REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Novelchew
Contributor II
Contributor II

Days difference between two dates.

Hi!

Need help to for QV to help calculate the difference between two dates.

Currently i'm using "date(floor([Due Date]-[Closed Date]),'DD/MM/YYYY') as DeltaDueClosed"

 

Issues that I'm facing,

1. My original data have timestamp behind, which I do not want it to calculate with the timestamp

2. Example Due Date = 13/10/20, Closed Date = 13/10/20.

The data outcome was -1, should be 0 instead.. 

 

Any idea how to get the correct data? 

1 Solution

Accepted Solutions
Kush
MVP
MVP

@Novelchew  You an individually floor it and then take a difference, of course  I am assuming that your both Date fields are in proper Date or timestamp format.

floor([Due Date])-floor([Closed Date]) as DeltaDueClosed"

View solution in original post

7 Replies
Taoufiq_Zarra
Master II
Master II

@Novelchew  like ?

floor(timestamp#([Due Date],'DD/MM/YYYY hh:mm:ss'))-floor(timestamp#([Closed Date],'DD/MM/YYYY hh:mm:ss')) as DeltaDueClosed
Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
Novelchew
Contributor II
Contributor II
Author

hello!
Tried it and the data all became "-" 😞

Taoufiq_Zarra
Master II
Master II

@Novelchew  can you share a sample data ?

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
Kush
MVP
MVP

@Novelchew  You an individually floor it and then take a difference, of course  I am assuming that your both Date fields are in proper Date or timestamp format.

floor([Due Date])-floor([Closed Date]) as DeltaDueClosed"

View solution in original post

Chanty4u
Champion III
Champion III

you can try this aswell

 

  date(timestamp#(date2,'MM/DD/YY'),'DD/MM/YYYY') as Dt2,

           date(timestamp#(date1,'MM/DD/YY'),'DD/MM/YYYY') as Dt1,

           Interval( (date(timestamp#(date2,'MM/DD/YY'),'DD/MM/YYYY')) -   (date(timestamp#          (date1,'MM/DD/YY'),'DD/MM/YYYY')), 'd') as DTDiff

 

or

Just use interval function as

Interval(Date1-Date2,'d')

 

or

 

Brett_Bleess
Support (Former)
Support (Former)

@Novelchew if one of the last two posts helped you resolve your issue, we would greatly appreciate you returning to the thread and closing it out by using the Accept as Solution button on the post(s) that helped.  If you did something different, you may post that and then use the button to mark it as the solution.  The posters receive credit for the help they provide by the Likes and Solutions they get marked on the Community, so it helps if you properly close things to be sure they get recognition for the assistance, and it helps others know what actually worked.k

Regards,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.
Novelchew
Contributor II
Contributor II
Author

Sorry for the late reply.

Thank you.. this worked and help!