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:

Groups	Number
group1	4
group1	5
group1	6
group2	7
group2	8
group1	9
group1	10
group2	11
group2	12
group1	13
group2	14
group1	15

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.

Groups	PREVIOUS_Number
group1	<null>
group1	4
group1	5
group1	6
group1	9
group1	10
group1	13
group1	15
group2	<null>
group2	7
group2	8
group2	11
group2	12
group2	14

Example 2

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

Given the following data:

Session	TimeStamp	url
session1	20	url1
session1	25	url2
session2	40	url1
session1	55	url3

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)

Session	Ascending_TimeStamp
session1	20
session1	25
session1	55
session2	40

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.

Session	Ascending_TimeStamp	url_PATH
session1	20	["external","url1"]
session1	25	["url1","url2"]
session1	55	["url2","url3"]
session1	55	["url3","external"]
session2	40	["external","url1"]
session2	40	["url1","external"]

Next use GROUP_PREVIOUS(#RawData!TimeStamp)

Session	Ascending_TimeStamp	url_PATH	Previous_TimeStamp
session1	20	["external","url1"]	<null>
session1	25	["url1","url2"]	20
session1	55	["url2","url3"]	25
session1	55	["url3","external"]	55
session2	40	["external","url1"]	<null>
session2	40	["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.

Session	Ascending_TimeStamp	url_PATH	Previous_TimeStamp	Time_Spent
session1	20	["external","url1"]	<null>	20
session1	25	["url1","url2"]	20	5
session1	55	["url2","url3"]	25	30
session1	55	["url3","external"]	55	0
session2	40	["external","url1"]	<null>	40
session2	40	["url1","external"]	40	0

Browser not supported