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: