Syntax

GROUP_DIFF(<number or date>; [<number>])

Description

Calculates the difference between the current value and the previous value seen in the group. The optional ["initial value"] argument is used as result for the first record. This defaults to null.

This is a group series function.

Examples

Given the following data:

Groups	Participants
group1	6
group1	12
group1	5
group1	5
group1	5
group1	7
group1	5
group1	9
group1	8
group2	24
group2	24
group2	24
group2	33
group2	33
group2	29
group2	30
group2	32
group2	35

First create a group, e.g. GROUPBY(#RawData!Groups)

Groups
group1
group2

Then use the GROUP_DIFF(#RawData!Participants), and the results show the difference between the current value the previous value in relation to the GROUPBY() column.

Groups	Participants_DIFF
groups1	<null>
groups1	6
groups1	-7
groups1	0
groups1	0
groups1	2
groups1	-2
groups1	4
groups1	-1
groups2	<null>
groups2	0
groups2	0
groups2	9
groups2	0
groups2	-4
groups2	1
groups2	2
groups2	3

Example 2

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

=GROUPBY(#Sheet1!session)
=GROUP_SORT_ASC(#Sheet1!timestamp)
=GROUP_PATH(#Sheet1!url)
=GROUP_DIFF(#Sheet1!timestamp)

Suppose we have this spreadsheet:

session	timestamp	url
session1	20	url1
session1	25	url2
session2	40	url1
session1	55	url3

The result sheet looks like this:

session	timestamp	click_path	time_spent
session1	20	["external":"url1"]
session1	25	["url1":"url2"]	5
session1	55	["url2":"url3"]	30
session1	55	["url3":"external"]
session2	40	["external":"url1"]
session2	40	["url1":"external"]

Note that the first value for a group in the time_spent column is empty, because there is no real time spent before you make the first request to the web server. You could use any other default by setting the second argument of GROUP_DIFF, e.g. GROUP_DIFF(#Sheet1!timestamp; 0). There are also empty (or null) values in this column for the last record of a group, because there is no time_spent that could be computed when leaving a web page. Leaving a web page is determined when there are no further requests, but you have no idea how long somebody spent on the last page he or she visits.

If you were to use GROUP_DIFF on two times stamps that use the second granularity, results are returned in milliseconds.

The sorting function GROUP_SORT_ASC is being used on the same sheet as where it is later being referenced by the function GROUP_DIFF. Insure the column being sorted is on the sheet where it is being referenced.

Browser not supported