cancel
Showing results for 
Search instead for 
Did you mean: 
Community_Admin
Community Team Member
Community Team Member
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
[DateRank] -1​
#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.
 
Rate this article:
Version history
Last update:
‎10-19-2021 03:26 PM
Updated by:
Contributors