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: 
rafaelsch
Contributor
Contributor

Subtract from date time field using Interval

Here is my scenario. I'm trying to subtract 7 days from a date time field.

Example: MyField : 15/12/2019 05:00  -> (dd/mm/yyyy hh:mm).
The result I'm expecting is :  08/12/2019 05:00   > (dd/mm/yyyy hh:mm).

When I remove the hour from MyField it works well . I use the interval function to do the job:

Date(interval([DATA] - 7,'D'),'DD/MM/YYYY'). The result is 08/12/2019 .

When I try something like this it doesn't work:

Date(interval([DataHra] - 7,'D'),'DD/MM/YYYY hh:mm').

Thanks in advance.

Labels (3)
1 Solution

Accepted Solutions
Taoufiq_Zarra

@rafaelsch  you can test for example :

=Timestamp(interval(Timestamp#(YOURFIELD,'dd/mm/yyyy hh:mm')-7,'D'))

 

for example :

=Timestamp(interval(Timestamp#('15/12/2019 05:00','dd/mm/yyyy hh:mm')-7,'D'))

the output is :

Capture.PNG

 

SET DateFormat='dd/mm/yyyy';
SET TimestampFormat='dd/mm/yyyy hh:mm]';

Regards,
Taoufiq ZARRA

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

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

View solution in original post

3 Replies
Taoufiq_Zarra

@rafaelsch  you can test for example :

=Timestamp(interval(Timestamp#(YOURFIELD,'dd/mm/yyyy hh:mm')-7,'D'))

 

for example :

=Timestamp(interval(Timestamp#('15/12/2019 05:00','dd/mm/yyyy hh:mm')-7,'D'))

the output is :

Capture.PNG

 

SET DateFormat='dd/mm/yyyy';
SET TimestampFormat='dd/mm/yyyy hh:mm]';

Regards,
Taoufiq ZARRA

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

(you can mark up to 3 "solutions") 😉
Kushal_Chawda

@rafaelsch   set the timestamp format as below based on your source data values.

SET TimestampFormat='DD/MM/YYYY hh:mm'

Then you can simply use below and you can also provide the format of your choice 

=timestamp(DATE_FIELD-7,'DD/MM/YYYY hh:mm')

rafaelsch
Contributor
Contributor
Author

It worked. Thanks a lot!