topic QlikView Time Subtraction in QlikView

QlikView Time Subtraction

Wed, 21 Aug 2013 09:50:26 GMT

Hi,

I need a help in qlikview.

I have four columns in a database named location, time, process center and actual time.

The location contain places like 'Arizona', 'Atlanta', 'New York' and so on...

Time contain date time format like '08/20/2013 03:04:18 PM', '08/21/2013 08:25:11 AM' etc....

Process Center Contain the values 'AZ', 'CO' and 'WC'.

The problem i am facing is that i need to make a pivot chart based on these values,

If the location is 'Atlanta' then i want to add 3 hours to the existing time and in today's case it will be 08/20/2013 06:04:18 PM.

If the location is 'Arizona', then i want to add -1 hours to the existing time and in today's case it will be 08/20/2013 02:04:18 PM.

Could you please help me to find out, how it can be done in QlikView

Thanks

Renjith Kumaran

Re: QlikView Time Subtraction

tresB — Wed, 21 Aug 2013 10:00:08 GMT

try like this:

=Timestamp(YourTimeStamp+Time#(1,'hh')) // to add 1 hour

In your case, use expression like:

If(location='Atlanta', Timestamp(YourTimeStamp+Time#(3,'hh')) )

Wait, I think you are converting time (based on time zone), if it is so, better use ConvertToLocalTime() function.

Re: QlikView Time Subtraction

Wed, 21 Aug 2013 10:11:17 GMT

Its working for adding 1 hour, but when i tried to subtract 1 hour, i got a wrong output..

Re: QlikView Time Subtraction

rbecher — Wed, 21 Aug 2013 10:18:59 GMT

Hi,

you can consider a timestamp as a float number where one day = 1. So to add one hour use 1/24:

// add 3 hrs:

=Timestamp(YourTimeStamp+3/24)

//subtract 1 hour:

=Timestamp(YourTimeStamp-1/24)

- Ralf

Re: QlikView Time Subtraction

tresB — Wed, 21 Aug 2013 10:21:45 GMT

probably you are trying like:

Timestamp(YourTimeStamp+Time#(-1,'hh')) // it would not work, because of the format specifier

try like:

Timestamp(YourTimeStamp - Time#(1,'hh'))

Re: QlikView Time Subtraction

Wed, 21 Aug 2013 10:25:03 GMT

Thanks Ralf, it worked...

Re: QlikView Time Subtraction

Wed, 21 Aug 2013 10:25:41 GMT

It works, many thanks...

Re: QlikView Time Subtraction

Tue, 05 Aug 2014 15:06:11 GMT

Hi,

Try this one to add some hours with your time stamp

Timestamp#(YourFieldName, 'YYYY-MM-DD hh:mm:ss') AS OldTime,

Timestamp(AddTime+Time#(5,'hh')) as NewTime

Re: QlikView Time Subtraction

Fri, 02 Oct 2015 11:18:40 GMT

Great, Works Perfect!!