
1. Re: Calculate rolling sales by customer based on first order date every year
Diego Parker Mar 16, 2019 7:09 AM (in response to Niraj Patel)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.
Please find the example attached.
Hope this helps.
If this helps you or answer your question, please mark it as helpful/correct so other users can refer to it. Thanks!
Best,
Diego

Example Rounded.twbx 1.2 MB


2. Re: Calculate rolling sales by customer based on first order date every year
Niraj Patel Mar 17, 2019 7:17 PM (in response to Diego Parker)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
Aditya Agarwal Mar 18, 2019 8:01 AM (in response to Niraj Patel)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,
Aditya

4. Re: Calculate rolling sales by customer based on first order date every year
Niraj Patel Mar 19, 2019 7:27 PM (in response to Aditya Agarwal)Hi Aditya,
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.

Sheet_1_data.xlsx 15.7 KB


5. Re: Calculate rolling sales by customer based on first order date every year
Aditya Agarwal Mar 19, 2019 9:42 PM (in response to Niraj Patel)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
Aditya Agarwal Mar 20, 2019 2:50 AM (in response to Niraj Patel)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,
Aditya

Rolling_Revenue.txt.zip 786 bytes

Rolling_Revenue_Results.csv.zip 559 bytes


7. Re: Calculate rolling sales by customer based on first order date every year
Niraj Patel Mar 24, 2019 10:18 PM (in response to Aditya Agarwal)Thank you Aditya.

8. Re: Calculate rolling sales by customer based on first order date every year
Aditya Agarwal Mar 25, 2019 11:00 PM (in response to Niraj Patel)Hi Niraj,
If is solves your problem , can you please mark the answer as helpful and close the thread.
Thanks,
Aditya