format of sum(time)

[marieclaire]

Hello everybody,

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

by hour , by device, by web site.

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

[swuehl]

Format the result of the aggregation using interval:

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

[marieclaire]

Thank you Stefan.

I tried your format :

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?

Thank you,

[swuehl]

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

[ziadm]

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')

[marieclaire]

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

Any better idea ?

[marieclaire]

Both give :

[swuehl]

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.

[marieclaire]

Thank you Stefan, it's better like this :

Thank you for your help !

best regards,

Christiane