8 Replies Latest reply on Mar 25, 2019 11:00 PM by Aditya Agarwal

# Calculate rolling sales by customer based on first order date every year

Hi, I have been trying to calculate rolling 12 month sales by a unique customer based on the first order date every year, however I can't seem to get the outcome that I'd like on Tableau online. Data is as below.

The output should essentially be for every year, i.e. 2017, 2018 and 2019, the first order date is identified, then for the next 12 months from that order date, the revenue is calculated. If i'm not mistaken, it has to be a dynamic calculation. I am aware there will be double revenue calculation with what I am requesting but that is fine.

I have tried the following

1. FirstOrderDate = {Fixed UserID,datepart('year', order_created_at): min(order_created_at)} to find the first order date every year

2. RollingDate = dateadd('months',12,FirstOrderDate) to calculate the corresponding end date for the first order date within a 12 months windown

3. Rolling revenue = {Fixed UserID, FirstOrderDate,RollingDate: sum(revenue)} to group the calculation by the first order date, corresponding rolling end date and user id, 1. however this does not seem to account for the revenue if moves on to the next year, i.e. 2017 to 2018

Order Created AtUserIDRevenue
2/15/2019112378.79828326
2/14/2019112352.90414878
2/12/20191123135.7081545
12/27/2018112366.0658083
11/11/20181123194.3919886
11/6/2018112342.88984263
10/23/2018112342.88984263
9/25/20181123143.6051502
9/4/20181123146.4663805
8/31/20181123146.4663805
8/15/2018112398.99856939
5/31/2018112337.19599428
5/14/2018112322.03147353
4/27/2018112345.77968527
4/27/2018112340.80114449
4/27/20181123227.4391989
2/27/2018112373.24749642
2/14/2018112361.80257511
2/13/2018112335.76537911
2/1/2018112354.33476395
1/29/20181123108.6695279
1/3/2018112331.44492132
11/9/2017112356.19456366
9/14/2017112340.0286123
8/16/2017112331.44492132
8/4/2017112325.72246066
6/6/2017112365.49356223
2/2/2017112314.27753934
1/6/2017112360.08583691
1/4/2017112360.08583691
1/4/2017112360.08583691
1/4/2017112360.08583691
• ###### 1. Re: Calculate rolling sales by customer based on first order date every year

Hey Niraj,

I believe this is much simpler to achieve if we add Year to the marks card and user id. This should divide our view by year and user and then we can just use a rolling calculation for each user ID.

Hope this helps.

Best,

Diego

• ###### 2. Re: Calculate rolling sales by customer based on first order date every year

Hi Diego,

Thank you for the reply however that is not what I was looking for.

To be specific, I am looking for

The total sum of revenue from the First order date. Therefore if the first order was on the 2nd Feb 2017, the total calculated should be from 2nd Feb 2017 to 2nd Feb 2018.

• ###### 3. Re: Calculate rolling sales by customer based on first order date every year

Hi Niraj ,

If possible can you attach an excel with some data points and expected results , so that it becomes easier for us to understand the query.

The Calculation which you have done looks correct , but would like to help you on the revenue calculation part.

In the shared sheet , the expected output is blank so it doesn't give proper context.

Thanks,

• ###### 4. Re: Calculate rolling sales by customer based on first order date every year

Sorry about that. I have now attached what is the output should look like. Here it is done on a one customer basis, hence I have blown up all the calculation parameters that should go in place in order to calculate the rolling revenue. Thank you for taking a crack at this.

• ###### 5. Re: Calculate rolling sales by customer based on first order date every year

Thanks Niraj,

Let me have a look and i will get back to you with my solution

• ###### 6. Re: Calculate rolling sales by customer based on first order date every year

Hi Niraj ,

I went through your requirement, Since in this case we need to duplicate rows for the rolling dates such as 3/01/018 exists under both min_order _date 4/01/2017 and 3/01/2018 .

It is not directly possible to do it without JOINS as the rows cant be duplicated.

Please find the attach SQL and results which you can use directly.

Let me know if it is of any help to you and would it be possible to use SQL in your Data Source.

Meanwhile i will try doing it on Tableau only and let you know if i have some other way .

Thanks,

• ###### 8. Re: Calculate rolling sales by customer based on first order date every year

Hi Niraj,

Thanks,