sum of hh:mm:ss tt

[alec1982]

anyone knows how to sum two fields with the format of hh:mm:ss tt?

[sunny_talwar]

I would probably do it like this:

Interval(Sum(TimeField1 + TimeField2), 'D hh:mm:ss')

[swuehl]

=Rangesum( Interval#(FIELD1, 'hh:mm:ss tt'),Interval#(FIELD2, 'hh:mm:ss tt') )

[MK_QSL]

=Interval(Timestamp#(Field1,'hh:mm:ss tt') + Timestamp#(Field2,'hh:mm:ss tt'),'hh:mm:ss')

[alec1982]

thank you all.. none of the above worked for me and here is why but still dont know the reason

the first field format is mm/dd/yyyy hh:mm:ss tt

the second field format is hh:mm:tt

I should've added that on my question..

Let me know how I can fix it and thank you for all your help!

Alec

[sunny_talwar]

So are you expecting a datefield result or just the sum of time for the two fields? I guess it would be helpful if you can share a couple of lines of input with their expected output here

[alec1982]

Sunny,

thank you for always helping us here..

to give you more thoughts the first field has a date and time for example: 1/25/2016 9:30:20 am

the second field is task average run time such as 1:30:20

the final field I need should return the sum of the start date and the average run time= 11:00:40 am

Hope this explains it..

[swuehl]

You should start with interpreting the field values using interpretation functions and appropriate format codes:

LOAD FIELD1,

          Timestamp#(FIELD1, 'MM/DD/YYYY hh:mm:ss tt') as FIELD1_,

             FIELD2,

             Interval#(FIELD2, 'hh:mm:tt') as FIELD2_

FROM YourTable;

Take care that the format codes are really matching the input values!

Check that all values in fields FIELD1_ and FIELD2_ now have a numeric representation.

Then you can sum the field values as you like:

Sum(FIELD1_) , Sum(FIELD2_), Sum(RANGESUM(FIELD1_, FIELD2_))

Finally, you can format the values using format functions: Timestamp(), Interval(), etc.

Why don’t my dates work?

[sunny_talwar]

May be something like this:

=Time(Time('1/25/2016 9:30:20 AM') + Time#('1:30:20', 'h:mm:ss'), 'hh:mm:ss TT')

Make sure TimeStamp and Time are read as TimeStamp/Time fields cause if they are not, then you will have to use TimeStamp#() and Time#() to help QlikView understand that they are TimeStamp and Time fields, respectively.

HTH

Best,

Sunny

[alec1982]

tried to build the two fileds but still getting the same format.. (Image attached)