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

    Niraj Patel

      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