|Integer and string manipulations ||
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.
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.
<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)"/>
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.