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
Did you mean:
Highlighted
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 ,

 Column1 Column2 Total 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

Labels (2)

• Timestamp

1 Solution

Accepted Solutions
Highlighted
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") 😉
4 Replies
Highlighted
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:

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:

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

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

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") 😉
Highlighted
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