Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Customers, Partners & Luminaries only: You're invited to a Data Analytics Roadmap session. Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
Contributor

how to calculate the total spend time from two columns

Hello Everybody, 

very new o qlikview.

 

I wanted to get the time spend between colum1 and column2 but it return nothing i use the formula below 

 


timestamp(timestamp#([Column1], 'd.hh:mm:ss') - timestamp#( [Column2], 'hh:mm:ss'),'hh:mm:ss'
) AS Total ,

Column1Column2Total 
2020-07-26 08:13:55.6562020-07-26 08:13:55.858 
2020-07-26 08:59:11.2122020-07-26 08:59:11.455 
2020-07-26 09:15:38.6372020-07-26 09:15:38.930 
   

 

Labels (2)
1 Solution

Accepted Solutions
Highlighted
Master
Master

if I understood correctly in your formula you need to add .ff in Time format

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉

View solution in original post

4 Replies
Highlighted
Master
Master

you can try this :

Timestamp(((Timestamp#(Column2,'YYYY-MM-DD hh:mm:ss.fff')-Timestamp#(Column1,'YYYY-MM-DD hh:mm:ss.fff'))), 'hh:mm:ss.ff') as Total 

 

 

One example:

 

Data:

LOAD *,
Timestamp(((Timestamp#(Column2,'YYYY-MM-DD hh:mm:ss.fff')-Timestamp#(Column1,'YYYY-MM-DD hh:mm:ss.fff'))), 'hh:mm:ss.ff') as Total 

 INLINE [
    Column1, Column2 
    2020-07-26 08:13:55.656, 2020-07-26 08:13:55.858
    2020-07-26 08:59:11.212, 2020-07-26 08:59:11.455
    2020-07-26 09:15:38.637, 2020-07-26 09:15:38.930
];

 

 

output:

Capture.PNG

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
Highlighted
Contributor
Contributor

Thank you so much , this helps. Can i ask you why the formula i use is not returning anything apart from column -column1

Highlighted
Master
Master

if I understood correctly in your formula you need to add .ff in Time format

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉

View solution in original post

Highlighted
Contributor
Contributor

Hello Taoufiq, 

 

thanks again , i try to get the year and month value also

 



Timestamp(((Timestamp#([Column2],'YYYY-MM-DD hh:mm:ss.fff')-Timestamp#([Column1],'YYYY-MM-DD hh:mm:ss.fff'))), ('YYYY-MM-DD hh:mm:ss.fff'))As total ,

 

it does return me the date value but it is giving me the date 1899-12-30 .It is something that i should fixed with the date value