topic Re: Days difference between two dates. in QlikView

Days difference between two dates.

Novelchew — Wed, 25 Nov 2020 16:16:04 GMT

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?

Re: Days difference between two dates.

Taoufiq_Zarra — Tue, 13 Oct 2020 07:36:23 GMT

@Novelchew like ?

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

Re: Days difference between two dates.

Novelchew — Tue, 13 Oct 2020 07:47:03 GMT

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

Re: Days difference between two dates.

Taoufiq_Zarra — Tue, 13 Oct 2020 07:53:39 GMT

@Novelchew can you share a sample data ?

Re: Days difference between two dates.

Kushal_Chawda — Tue, 13 Oct 2020 11:35:26 GMT

@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"

Re: Days difference between two dates.

Chanty4u — Tue, 13 Oct 2020 12:10:00 GMT

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

Just use interval function as

Interval(Date1-Date2,'d')

Re: Days difference between two dates.

Brett_Bleess — Tue, 27 Oct 2020 15:57:07 GMT

@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

Re: Days difference between two dates.

Novelchew — Thu, 29 Oct 2020 02:27:16 GMT

Sorry for the late reply.

Thank you.. this worked and help!