5 Replies Latest reply on Apr 5, 2016 9:33 AM by Kieran STANLEY

# LOD: Sum by user id over fixed date range

I have a list of customer ids, revenue amounts and revenue dates. I'd like to sum revenue per customer over a fixed date range (let's say the first 30 days following their first transaction) and use this calculation in views with longer date ranges.

Is there a way to do this with level of detail calculations or something else?

• ###### 1. Re: LOD: Sum by user id over fixed date range

Hi Kieran

Please find the attached workbook and data sheet. If this what you want, I can explain the steps you need to follow

Columns : Day of the date would be the first transaction day for given customer id and display the total transaction amount for first 30 days (this can be parameterise). My sample data set I used 5 different start dates for 5 given customers.

Thanks

• ###### 2. Re: LOD: Sum by user id over fixed date range

Thanks for responding Shanaka. Would you mind walking me through your example step-by-step?

• ###### 3. Re: LOD: Sum by user id over fixed date range

Hello Kieran - What you are looking for is the Customer level first 30 days (Parameterised) of Sales transactions. For that you need to follow three simple steps :

Step 1: Create a calculated field called

"First Purchase date" and the calculated expression would be {FIXED [Customer Id]: MIN([Order Date])}

This will return first purchase date for each customer

Step 2: Create a parameter called "Date Range" of type Integer and default value as 30: This will allow to receive input from user. This value can be changed from the dashboard.

Step 3: Create another Calculated field called "Customer Revenue" and that should contain the following code

if [Order Date]>= [First Purchase date]

AND [Order Date]<= DATEADD('day',[Date Range],[First Purchase date])

THEN [Revenue]

END

This will provide revenue for the Customer for first 30 (based on the parameter input) days of Revenue.

Now use this field "Customer Revenue" and "Order Date" to create any type of Visualization.

Thanks,

Manideep

• ###### 4. Re: LOD: Sum by user id over fixed date range

Hi Kieran

I didn't use LOD so my solution would be bit complicated. Manideep Bhattacharyya  has used LOD which is more easy to handle your problem (hope your using Tableau 9 or above). Both are working fine. I m attaching Manideep solution (Sheet2) as well.

Steps:

1. Create Calculated field (LOD) to get the first transaction date for customer   (Name: Calc_First_Revenue)

{FIXED [Customer Id]:Min([Date])}

2. Create another calculated field to get the revenue for customer for period  (Name: Calc_Cust_revenue)

IF([Date])  >= [Calc_First_Revenue]

AND [Date]<=DATEADD('day',30,[Calc_First_Revenue]) THEN [Revenue]END

Note: Period has declared as 30, you can use a parameter to pass the value

3. Add Calc_First_Reveue to Columns. Right Click select day and Discrete

4. Add Customer id to Rows

5. Add Calc_Cust_Revenue to Text Marks

• ###### 5. Re: LOD: Sum by user id over fixed date range

I do have Tableau 9 so the LOD solution is the most straightforward. Thank you both!