topic Re: Subtract from date time field using Interval in QlikView

Subtract from date time field using Interval

rafaelsch — Sat, 16 Nov 2024 18:02:34 GMT

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.

Taoufiq_Zarra — Tue, 29 Sep 2020 13:31:11 GMT

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

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

Kushal_Chawda — Tue, 29 Sep 2020 14:42:55 GMT

@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 — Tue, 29 Sep 2020 15:05:49 GMT

It worked. Thanks a lot!