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

    LOD: Sum by user id over fixed date range

    Kieran STANLEY

      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
          Shanaka Gunaratna

          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
            Kieran STANLEY

            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
              Manideep Bhattacharyya

              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
                Shanaka Gunaratna

                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
                  Kieran STANLEY

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