Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
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