g_twindow(G;S;T;TS;N)

Returns the number of rows to the row that is exactly a certain time period before (or after) the current row within a given group.

Function type

Vector only

Syntax

g_twindow(G;S;T;TS;N)
t_twindow(N)

Input

Argument Type Description
G any A space- or comma-separated list of column names

Rows are in the same group if their values for all of the columns listed in G are the same.

If G is omitted, all rows are considered to be in the same group.

If any of the columns listed in G contain N/A, the N/A value is considered a valid grouping value.
S integer The name of a column in which every row evaluates to a 1 or 0, which determines whether or not that row is selected to be included in the calculation

If S is omitted, all rows will be considered by the function (subject to any prior row selections).

If any of the values in S are neither 1 nor 0, an error is returned.
T integer or decimal The name of a column whose values are used as the basis of the time series

Row order is determined by T.

Note: T may not be omitted.
TS text A string representing the type of the time T

The choice of TS must match the type of values in T, or the results will be meaningless.

Valid types are:
  • 'D' for the date (YYYYMMDD) type (i.e., one period = one day)
  • 'Q' for the quarter (YYYYQ) type (i.e., one period = one quarter)
  • 'M' for the month (YYYYMM) type (i.e., one period = one month)
  • 'T' for the time (HHMMSS) type (i.e., one period = one second)
  • 'TS' for the date+time type (i.e., one period = one second)
  • 'TSn' for the date+time type construed with n decimal places of precision in the seconds place (i.e., one period = 1/10^n seconds)

If TS is omitted, then T is assumed to be an integer (e.g., a year or a period number, such that one period=1).
N
  • integer
  • big integer
 The number of rows to shift within the group
Note: Support for columns of type big integer available as of version 11.26.

Return Value

For every row in each group defined by G and ordered by T (and for those rows where S=1, if specified):
  • If N >= 0:

    • g_twindow returns the number of rows from the current row to the row that is exactly N time periods after this row in the same group as this row.

      If there are multiple rows that are exactly N time periods after this row, g_twindow returns the number of rows to the first row within that set of rows.

    • If there are no rows that are exactly N time periods after this row, g_twindow returns N/A.

  • If N < 0:

    • g_twindow returns the number of rows from the current row to the row that is exactly |N| time periods before this row in the same group as this row.

      If there are multiple rows that are exactly |N| time periods before this row, g_twindow returns the number of rows to the first row within that set of rows.

    • If there are no rows that are exactly |N| time periods before this row, g_twindow returns N/A.

Time periods are determined by TS with respect to the values in T.

The result is the same data type as X.

Note: N/As in X are treated like any other value; i.e. they are shifted.

If no rows in a group have valid (non-N/A) values for X, the result for every row of the group is N/A.

Sample Usage

<base table="pub.doc.samples.ref.func.g_func_time_series_sample_usage"/>
<willbe name="g_twindow_1" value="g_twindow(state;include;order;;-1)"/>
<willbe name="g_twindow_2" value="g_twindow(state city;include;order;;-1)"/>

Example

For this example, we'll use the sales data available in pub.demo.retail.item, which contains sales transactions from three stores.

Let's say we want to find out the number of rows from the current row to the previous day. To do this, we can use g_twindow.

For simplicity, let's say we just want to look at the transactions from store 1. We'll do a simple <sel> operation to pare down the data: 
<sel value="(store=1)"/>
(Obviously, this is not too critical for our sample table, which only contains 35 rows, but it will make our example a little easier to understand).

Now our table just shows the transactions from store 1:

The g_twindow function has the form:

g_twindow(G;S;T;TS;N)

Since we already selected the rows for store 1, which is the group we're interested in, we can omit the G parameter. (If we hadn't done the <sel> operation earlier, we could have set G to store here in order to group the results by store.)

We can also omit the S parameter, since we want to consider all rows when applying the function.

We'll use the date column for our T parameter and set the TS parameter to 'D' (since the data in the date column is of the date type), and we'll set the N parameter to -1, which will allow us to shift back to the previous day in our table.

So, our g_twindow function should look like:
g_twindow(;;date;'D';-1)
Note: Even though we're omitting the G and S parameters, we still need to account for them in the function call; hence, the ";;" before date.
Let's add a computed column to our table with this g_twindow function using <willbe>:

<willbe name="rows_to_previous_day" label="# Rows to`Previous Day" value="g_twindow(;;date;'D';-1)"/>

The results of this operation will look like:

Notice all the N/A values in our new column. That's because the only data we have for consecutive days is on 05/15/12 and 05/16/12, and g_twindow only returns a non-N/A result when there is an exact match with respect to the time period specified. So, for each row with the date 05/16/12, g_twindow returns the number of rows to the previous day, 05/15/12.

Note: Although there are multiple rows with the date 05/15/12, g_twindow returns the number of rows to the first row with that date.

So, in our example, for the first transaction with the date 05/16/12, g_twindow returns a -3, meaning that you need to shift back 3 rows in the table to get to the previous day. For the second transaction with the date 05/16/12, g_twindow returns a -4, meaning that you need to shift back 4 rows in the table to get to the previous day.

All the other transactions are more than one day apart; therefore, g_twindow returns N/A because there is no exact match with respect to the time period specified.

If you wanted to find the nearest row whether there was an exact match or not with respect to the time period specified, you could use g_twindow_nearest.

Additional Information

  • The t_ version of this function defaults the G and T/TS arguments, and omits the S argument. The defaults for G and T/TS are set at table load time based on the organization of the table.