Create session ID from data with timestamp

[#1]

Hi everyone,

I'm working with the server Audit data, which shows user activity in my QV dashboards. Each action has its own row and a timestamp. It looks something like this:

Timestamp	Document	User	Message
7/15/2015 11.19:52	Analysis.qvw	John	Selected XYZ
7/15/2015 11.21:46	Analysis.qvw	John	Activated Sheet
7/15/2015 11.21:52	Analysis.qvw	John	Selected ABC
7/15/2015 11.22:13	Budget.qvw	John	Export to Excel
7/15/2015 11.22:30	Budget.qvw	John	asdasd
7/15/2015 11.22:14	Analysis.qvw	Mary	yj
7/15/2015 11.22:25	Analysis.qvw	Mary	ty
7/15/2015 1.15:05	Analysis.qvw	John	adfh
7/15/2015 1.17:10	Analysis.qvw	John	adfaer

In the script, I want to use Timestamp and User to create a Session ID.

So John from 11.19:52 - 11.22:30 would be Session ID 1, (ignore him switching dashboards during session)

Mary is 11.22:14-11.22:25, 2

and then when John comes back later from 1.15:05-1.17:10 would be Session 3.

I think this can be done with a condition that says after 10 minutes from last user timestamp, it counts as a new session, so create a new session ID. But I don't know how to write this. Can anyone help me?

[swuehl · Accepted solution]

Timestamp format is OK, but it's

ORDER BY User, Timestamp desc;

edit:

Attached the sample, now also with a resorted SessionID.

[#3]

This is SO COOL! Thank you Swuehl!

[#4]

Swuehl,

I've posted a new but related question here, if you are interested: Difference between timestamps, within sessions

[#5]

Thanks for your help Marcus. I did not know about the Class function, I will keep it in mind for other problems

[#6]

Hi Swuehl

rangesum(1, Peek(Session)), could you please  explain us what it  will do.

here 1 means all? or from the top of the row?

[swuehl]

rangesum(expr1 [ , expr2, ... exprN ])

Returns the sum of a range of 1 to N arguments. As opposed to the + operator, rangesum will treat all non-numeric values as 0.

Examples:

rangesum (1,2,4) returns 7

rangesum (1,'xyz') returns 1

rangesum (null( )) returns 0