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:
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.