3 Replies Latest reply on Oct 12, 2016 4:28 AM by Hima vardhan Reddy Pavuluri

# Level of detail - finding next date

I have a list of customer transactions that includes a unique identifier for the customer and a date for each of their transactions.  I'm able to use the level of detail calculation to define the minimum and maximum transaction date for each customer (and this is applied to each row when I look at the data).  What I would like to do is for each customer transaction: show what the date is of the very next transaction for that customer (rather than the minimum or maximum).

My goal is for each customer transaction understand when the next transaction was and how many days apart these were.

• ###### 1. Re: Level of detail - finding next date

Try with

iif(Date > 1stPurchase, Date,null)

where Date is original date column and 1st purchase is LOD minimum calculation for date.

• ###### 2. Re: Level of detail - finding next date

Hi Andrew,

Thanks,

Sreekanth.

• ###### 3. Re: Level of detail - finding next date

Hi ,

I have taken a sample data set and did the required work to get next date output.

 Customer Date c1 12/3/2016 c1 8/2/2016 c1 9/9/2016 c2 2/2/2016 c3 1/1/2016 c2 5/7/2016

This is sample data. This is the output :

create a calculation named rank like this.

RANK(ATTR([Date]))

Drag it into rows shelf and go to edit table calculations and go to advanced in compute using option and give customer in partitioning and Date in Addressing then drag rank into filters shelf and exclude Null.

Sort Date field in Rows shelf in ascending order using Date filed.

Then create final required output calculation named Next_Date like below.

LOOKUP(ATTR([Date]),1)

Drag this into Rows shelf. This is the next transaction date for our current transaction date for that particular customer.

Note : Date filed in sample set is in MM/DD/YYYY format.

Hope this helps you.

You can do it without using Rank calculation also but if you wont use rank calculation it will take the next customer first date for the last transaction date of current customer.