Page tree
Skip to end of metadata
Go to start of metadata

Syntax

GROUPBYGAP(<number, date>;<number>)

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.
  • No labels