topic how to substract a date in QlikView

how to substract a date

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

hi guys, i want to ask how to substract a date,

for example,
payment date = 12/12/18

invoice date = 12/10/18

-i need an interval value between the date.

-in that case we already know, the difference is 2.

[payment date]-[invoice date],

but how to get the value??

thank you

uacg0009 — Wed, 19 Dec 2018 06:54:47 GMT

Hi Davinfrost,
I think you can do it directly.
Use: [payment date]-[invoice date] in your expression or script

Aiolos

phillipmacd — Wed, 19 Dec 2018 07:10:53 GMT

Hi, try

Interval(Date([payment date],'MM/DD/YY') - Date([invoice date],'MM/DD/YY'),'D')

tresB — Wed, 19 Dec 2018 07:14:57 GMT

If your date fields are really dates, i.e , number at the back end. You could get the difference in days like:

Interval([payment date]-[invoice date], 'D')

Also subtracting one from another (like Aiolos showed above.) could give you the right number if your date fields don't have timestamp component.

fallenangel6 — Wed, 19 Dec 2018 08:25:07 GMT

i convert dates into floor in backend and then use it for finding difference if i dont need to worry about timestamps.

else you can use below format

interval(EndDate - StartDate,'D hh:mm')

gives output in Day hrs and minute if you have timestamp.