GROUPBYGAP

Syntax

Description

This functions displays values by defined gap sizes from a column. A GROUPBY() function must first be used in order to then find the gaps in arguments of another column in relation to it. When run, the function sorts the numbers or dates in ascending order by group. The results displayed are the data values greater than the user made "max gap" value. These gaps are defined as being from each value to the next subsequent value.

This function can be used for click stream analysis. To do click stream analysis, this function also does a secondary sort on the argument column.

This is a group series function.

Example

The following example extracts sessions from the timestamp column when there is more than a 7 time-unit gap:

Suppose you have this spreadsheet:

User	Timestamp
Ajay	1
Ajay	2
Ajay	3
Ajay	11
Ajay	14
Ajay	21
Mary	2
Mary	4
Mary	6
Mary	11
Mary	21
Daniel	3
Daniel	12
Daniel	9
Daniel	6
Daniel	21
Daniel	18
Daniel	15

First create a group using GROUPBY(<user>)
Then use the GROUPBYGAP(<timestamp>;7)
The result of applying GROUPBYGAP would be:

user	result_timestamp
Ajay	1
Ajay	11
Mary	2
Mary	21
Daniel	3

If the first parameter is a date:

A number entered in the second parameter to define the gap will be represented in milliseconds.
A date constant can be used in the second parameter to define the max gap.