GROUP_PREVIOUS

Syntax

GROUP_PREVIOUS(<any>;[<number>])

Value: series value

K: lag

Description

Returns the value of the set k'th previous record in series. This can be used for introducing a lag for time series analysis and/or computing autocorrelation.

This is a group series function.

Example

Given the following data:

GroupsNumber
group14
group15
group16
group27
group28
group19
group110
group211
group212
group113
group214
group115

First create a group using GROUPBY(#RawData!Groups).

Groups
group1
group2

Then use the GROUP_PREVIOUS(#RawData!Number). The result shows the previous record's value in relation to the GROUPBY() column.

GroupsPREVIOUS_Number
group1<null>
group14
group15
group16
group19
group110
group113
group115
group2<null>
group27
group28
group211
group212
group214

Example 2

The following example illustrates how to compute the time a user has spent on a web site.

Given the following data:

SessionTimeStampurl
session120url1
session125url2
session240url1
session155url3

First create a group using GROUPBY(#RawData!Session).

Session
session1
session2

In order for the GROUP_PREVIOUS function to work, you need to use GROUP_SORT_ASC in conjunction. Sorting data isn't sufficient.

Next sort your timestamp in ascending order using GROUP_SORT_ASC(#RawData!TimeStamp)

SessionAscending_TimeStamp
session120
session125
session155
session240

Next use the GROUP_PATH(#RawData!url). The result shows paths for all values of the column in ascending order in relation to the GROUPBY() column.

SessionAscending_TimeStampurl_PATH
session120["external","url1"]
session125["url1","url2"]
session155["url2","url3"]
session155["url3","external"]
session240["external","url1"]
session240["url1","external"]

Next use GROUP_PREVIOUS(#RawData!TimeStamp)

SessionAscending_TimeStampurl_PATHPrevious_TimeStamp
session120["external","url1"]<null>
session125["url1","url2"]20
session155["url2","url3"]25
session155["url3","external"]55
session240["external","url1"]<null>
session240["url1","external"]40

Finally use DIFF(#Sheet1!Ascending_TimeStamp;#Sheet1!Previous_TimeStamp) to subtract the ascending TimeStamp from the Previous_TimeStamp to find how long the user spend on the URL.

SessionAscending_TimeStampurl_PATHPrevious_TimeStampTime_Spent
session120["external","url1"]<null>20
session125["url1","url2"]205
session155["url2","url3"]2530
session155["url3","external"]550
session240["external","url1"]<null>40
session240["url1","external"]400