topic Re: format of sum(time) in QlikView

format of sum(time)

marieclaire — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: format of sum(time)

swuehl — Tue, 25 Apr 2017 21:29:57 GMT

Format the result of the aggregation using interval:

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

Re: format of sum(time)

marieclaire — Thu, 27 Apr 2017 13:21:24 GMT

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,

Re: format of sum(time)

swuehl — Thu, 27 Apr 2017 13:26:23 GMT

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

Re: format of sum(time)

ziadm — Thu, 27 Apr 2017 13:31:20 GMT

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

Re: format of sum(time)

marieclaire — Thu, 27 Apr 2017 13:35:25 GMT

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

Any better idea ?

Re: format of sum(time)

marieclaire — Thu, 27 Apr 2017 13:45:25 GMT

Both give :

Re: format of sum(time)

swuehl — Thu, 27 Apr 2017 13:46:01 GMT

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.

Re: format of sum(time)

marieclaire — Thu, 27 Apr 2017 13:53:40 GMT

Thank you Stefan, it's better like this :

Thank you for your help !

best regards,

Christiane