QlikView Time Subtraction

[#1]

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

[tresesco]

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. 

[#3]

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

[rbecher]

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

[tresesco]

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'))  

[#6]

Thanks Ralf, it worked...

[#7]

It works, many thanks...

[#8]

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

[#9]

Great, Works Perfect!!