GROUPSESSIONS
Syntax
GROUPSESSIONS(<string, date, number>; <string, date, number>; <string, date, number>; <string, date, number>; <string, date, number>)
Description
Assigns IDs to events based on session start and session end. All events with the session ID of the start event between start and end event get assigned the ID of the start event.
This is a group series function.
Example
Given the following raw data:
| GroupName | Event | Date |
|---|---|---|
| 1 | A | Aug 1, 2014 |
| 1 | B | Aug 2, 2014 |
| 2 | start | Aug 3, 2014 |
| 2 | A | Aug 4, 2014 |
| 2 | A | Aug 5, 2014 |
| 2 | end | Aug 6, 2014 |
| 3 | B | Aug 7, 2014 |
| 3 | start | Aug 8, 2014 |
| 3 | end | Aug 9, 2014 |
| 3 | A | Aug 10, 2014 |
Create a new worksheet and use the GROUPBY function to group by the GroupName column from the raw data.
GROUPBY(#rawdata!GroupName)
COPY the event and date columns for reference.
COPY(#rawdata!Event) COPY(#rawdata!Date)
| GroupName | Event | Date |
|---|---|---|
| 1 | A | Aug 1, 2014 |
| 1 | B | Aug 2, 2014 |
| 2 | start | Aug 3, 2014 |
| 2 | A | Aug 4, 2014 |
| 2 | A | Aug 5, 2014 |
| 2 | end | Aug 6, 2014 |
| 3 | B | Aug 7, 2014 |
| 3 | start | Aug 8, 2014 |
| 3 | end | Aug 9, 2014 |
| 3 | A | Aug 10, 2014 |
Use the GROUPSESSIONS function to create a column that assigns a session ID for each event with the same session ID of the "start" marker between the "start" and "end" markers.
Events = #rawdata!Event
Start event = "start"
End event = "end"
Session ID = #rawdata!GroupName
OrderBy = #rawdata!Date
GROUPSESSIONS(#rawdata!Event;"start";"end";#rawdata!GroupName;#rawdata!Date)
| GroupName | Event | Date | SessionId |
|---|---|---|---|
| 1 | A | Aug 1, 2014 | |
| 1 | B | Aug 2, 2014 | |
| 2 | start | Aug 3, 2014 | 2 |
| 2 | A | Aug 4, 2014 | 2 |
| 2 | A | Aug 5, 2014 | 2 |
| 2 | end | Aug 6, 2014 | 2 |
| 3 | A | Aug 10, 2014 | |
| 3 | B | Aug 7, 2014 | |
| 3 | start | Aug 8, 2014 | 3 |
| 3 | end | Aug 9, 2014 | 3 |