Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Pull seconds out of timestamp, use in functions

Hey,

Got a small issue here.  I have a data set with two timestamps, and in my script I'm pulling the interval between them and storing it in a column.  That interval is in 'ss' timestamp format, becuase I only want seconds:

Interval(AcceptTimestamp - CreateTimestamp, 'ss') as [Answer Time],

This gives me a column with only the second intervals, that I can them sum.

Only when I try to run AVG function like this:

=Avg([Answer Time])

to grab the average interval in whole seconds

Its grabbing the number number format (since qlikview stores date in two formats the string and the internal number)

and averaging off that , instead of the whole ss number I'm storing.  If your familiar with how qlikview stores timestamps you'll understand that, lol

does anyone know how to use avg function on units of time pulled off a datestamp?  Should I store the seconds as integers?

1 Solution

Accepted Solutions
MVP
MVP

Re: Pull seconds out of timestamp, use in functions

Num#(Interval(AcceptTimestamp - CreateTimestamp, 'ss'))  as [Answer Time in sec],

This will store your interval seconds as integers, not as fraction of days.

5 Replies

Re: Pull seconds out of timestamp, use in functions

Have you tried wrapping the average in the interval() function?

=Interval(Avg([Answer Time]),'ss')

Re: Pull seconds out of timestamp, use in functions

Interval works -> here is a sample attached

MVP
MVP

Re: Pull seconds out of timestamp, use in functions

Hi,

Try like this

Table:

Load *,

Interval(DateField1 - DateField2, 'ss')*24*60*60 as Seconds

FROM DataSource;

Now you can directly use Avg(Seconds) to get the average.

Hope this helps you.

Regadrs,

Jagan.

mohammadkhatimi
Valued Contributor

Re: Pull seconds out of timestamp, use in functions

Hie..


Load *,

Interval(AcceptTimestamp - CreateTimestamp, 'ss')*24*60*60  as Sec

FROM table

---  24*60*60 is to convert days into secounds.

Afterwards use Avg(Sec)

Regards,

Mohammad.

MVP
MVP

Re: Pull seconds out of timestamp, use in functions

Num#(Interval(AcceptTimestamp - CreateTimestamp, 'ss'))  as [Answer Time in sec],

This will store your interval seconds as integers, not as fraction of days.

Community Browser