Introduction
Calculating transaction frequency for a given dimension is a common requirement across many verticals. e.g. Average Time Between Purchases by a Customer. Average Time between support tickets etc.
While this can be calculated in SQL it can get rather complex. A simpler way to achieve this is via custom columns using the rankasc and lookup functions.
Instructions
For this example we shall consider sales orders.
We wish to find the average time in days between orders placed by a given customer.
Create the following custom fields
#1 DateRank
RankAsc([CUSTOMER_ID],[Order Date])
#2 DateRankPrev
#3 CustomerRank - A combination of the dimension PK and the rank
tostring([CUSTOMER_ID]) + '-' + tostring([DateRank])
#4 CustomerRankPrev
tostring([CUSTOMER_ID]) + '-' + tostring([DateRankPrev])
#5 OrderDayDiff
daydiff([Order Date], Lookup([ORDERS1],[Order Date],CustomerRankPrev
The OrderDayDiff provides the number of days between the current transaction and the previous for the specific client.
Averaging this field in the dashboard widgets to obtain the desired numbers.
