Showing results for 
Search instead for 
Did you mean: 
Community Team Member
Community Team Member
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. 
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.
Version history
Last update:
‎10-19-2021 03:26 PM
Updated by:
Community Toolbox

Recommended quick links to assist you in optimizing your community experience:

Developers Group:

Product Feedback Forum:

Need additional support?:

Submit a Support Request

The Legal Stuff

Have a question about the Sisense Community?

Email [email protected]

Share this page: