Determining days between entries

Using a combination of days and g_rshift, you can determine how many days have elapsed between the previous occurrence of a particular value and the current entry.

Difficulty

Objective

You want to determine how many days have passed from the last occurrence of a particular value to the current entry. For example, you might want to know how long it has been since the adjusted closing price of a stock was over a certain amount or how many days have passed since a customer last visited a particular store.

Solution

<base table="pub.doc.retail.altseg.sales_detail_transid"/>
<sel value="customer='2768ac88'"/>
<tabu label="Visit Dates" breaks="customer,store,trans_date">
  <tcol source="trans_date" fun="first" name="visit_date" label="Date of Visit"/>
</tabu>
<colord hide="date"/>
<willbe name="store103" value="store=103" label="Visited Store 103"/>
<willbe name="days_since" label="Days Since`Last Visit" 
value="days(g_rshift(customer;store103;date;date;-1);trans_date)"/>

Discussion

This recipe determines the number of days between a customers most recent two visits.

In order to use the appropriate G argument later in the query, you need to perform a tabulation because the table is segmented by transaction ID and not by customer. You can use fun="first" to obtain the first instance of every date.

A reference column named store103 to identify the rows in which the customer visited store 103. The function g_rshift(G;S;O;X;N) finds the previous occurrence within that group of rows in relation to the current row, and the days(X;Y) function determines the number of days between them.

Further reading

If you would like to learn more about the functions and operations discussed in this recipe, click on the links below:

g_rshift(G;S;O;X;N)

days(X;Y)