topic Re: Number format time function in QlikView

Number format time function

Anonymous — Fri, 09 Mar 2012 10:18:11 GMT

Hi all,

From the Google Analytics API I get total visits and total time on site. When I divide the total time on site by total visits I expect the average time on site.

But, and here is the question, the total time is: 1.014374E7. This number supposed to be the total seconds. But when I make an expression timeOnSite / visits it doesn't work.

I have already tried to format it into: interval(num#(metric_timeOnSite, '#.#' ,'.',',')/(24*60*60),'h:mm:ss'), but that gives me null.

Any thoughts on this? Thanks in advance.

Regards,

Henco

Number format time function

CELAMBARASAN — Fri, 09 Mar 2012 10:43:13 GMT

Hi,

Did you tried with this?

interval(num(metric_timeOnSite)/(24*60*60),'h:mm:ss')

If it didn't works then try with this.

interval((num(SubField(metric_timeOnSite,'E',1))*pow(10,SubField(metric_timeOnSite,'E',2)))/(24*60*60),'h:mm:ss')

Hope it helps

Celambarasan

Re: Number format time function

Anonymous — Fri, 09 Mar 2012 11:02:58 GMT

Hi Celambarasan,

Thanks for your reply. Unfortunately both of them don't work.

Find attached the sample document, with the values in it.

Regards,

Henco

Re: Number format time function

swuehl — Fri, 09 Mar 2012 23:10:07 GMT

Henco,

please take care of the decimal point format and thousand separator. Again, I think the initial load used the wrong settings.

I fixed this with a num#() function, but I would prefer to handle this with standard format settings.

Then you can evaluate your timeOnSite to get the scientific notation correctly in.

Please see also attached.

Stefan

Re: Number format time function

Anonymous — Sat, 10 Mar 2012 07:32:27 GMT

Thank you very much Stefan.

You helped me out!

Regards,

Henco