Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
alec1982
Specialist II
Specialist II

sum of hh:mm:ss tt

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

1 Solution

Accepted Solutions
swuehl
MVP
MVP

Pls find attached.

View solution in original post

15 Replies
sunny_talwar

I would probably do it like this:

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

swuehl
MVP
MVP

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

MK_QSL
MVP
MVP

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

alec1982
Specialist II
Specialist II
Author

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
Specialist II
Specialist II
Author

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
MVP
MVP

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
Specialist II
Specialist II
Author

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