Calculate Average Time Between Transactions

[Community_Admin]

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.