GROUP_DIFF

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:

GroupsParticipants
group16
group112
group15
group15
group15
group17
group15
group19
group18
group224
group224
group224
group233
group233
group229
group230
group232
group235

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.

GroupsParticipants_DIFF
groups1<null>
groups16
groups1-7
groups10
groups10
groups12
groups1-2
groups14
groups1-1
groups2<null>
groups20
groups20
groups29
groups20
groups2-4
groups21
groups22
groups23

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.