Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

format of sum(time)

Hello everybody,

I made a chart to display the audience of a website  :

by hour , by device, by web site.

QW_format_timespent.png

The metric is "m_time_spent" (seconds)

I need to translate the sum(m_time_spent) in a time format as dd:hh:mm:ss

It does not work with :

sum( {$<d_site={MyWebSite}>} time(1/24/60/60 *m_time_spent))

Any idea ?

Thank you for your help,

Christiane

Tags (1)
8 Replies
Highlighted
MVP
MVP

Re: format of sum(time)

Format the result of the aggregation using interval:

=Interval( Sum(m_time_spent) / (24*60*60) ,'hh:mm:ss')

Highlighted
Creator
Creator

Re: format of sum(time)

Thank you Stefan.

I tried your format :

QW_format_timespent_test1.png

Then i tried to convert "mm" into "dd" :

=Interval( Sum(m_time_spent) / (30*24*60*60) ,'dd:hh:mm:ss')

Is it the right syntax and the right format?

QW_format_timespent_test2-days.png

Thank you,

Highlighted
MVP
MVP

Re: format of sum(time)

Why are you dividing by an additional factor 30? The division should not depend on your interval format.

Highlighted
Specialist
Specialist

Re: format of sum(time)

Hi Try this

=Time(floor(Sum(Time)/3600) & ':'  & Floor((Sum(Time)/3600 - floor(Sum(Time)/3600) )*60 ),'HH:MM')

OR this also should do

time((Sum(Time)/86400),'HH:mm:ss')

Highlighted
Creator
Creator

Re: format of sum(time)

I tried to estimate "4392" days into intervals of 30 days, to make the metric easier to understand.

Any better idea ?

Highlighted
Creator
Creator

Re: format of sum(time)

Both give :

QW_format_timespent_test3-days.png

Highlighted
MVP
MVP

Re: format of sum(time)

4392 in your first chart denotes hours, not days. That should calculate to 183 days, if you use a format 'd'.

If you want to show month instead of days, hours, minutes etc. then maybe use something like

=Sum(m_time_spent) / (30.5*24*60*60)

(30.5 as average month duration in days, no interval formatting, since it doesn't support month format.

Highlighted
Creator
Creator

Re: format of sum(time)

Thank you Stefan, it's better like this :

QW_format_timespent_test4-days.png

Thank you for your help !

best regards,

Christiane