7 Replies Latest reply on Jun 26, 2018 3:34 AM by Rajeswari S

    Month Range Buckets Using Date Field

    Leanne Vermeulen

      I want to create monthly buckets using date fields to show which customers made purchases in the last N months. The monthly buckets would look like this:

      0 - 6 months

      7 - 12 months

      13 - 18 months

      19 + months

       

      The method used in this post works great, but only when creating buckets from days: How to create Bucket using Date Field?

      As I want to use months instead of days, this method doesn't work because it seems anything more than 12 starts back at January (13 = January, etc.) so instead of filling the "13 - 18 months" bucket, the data falls into the "0 - 6 months" bucket.

       

      On a side note, as I'm basing these buckets on a customer's latest purchase, I use the FIXED LoD in my calculation to link Customer ID to their latest purchase: {FIXED [CustID] : MAX(DATEPART('month',[Createdate]))}

       

      So the final view would show all customers / count of customers who made their last purchase within one of the above monthly buckets.

        • 1. Re: Month Range Buckets Using Date Field
          Andrew Kim

          Try:

           

          IF DATEDIFF('month', [date], {FIXED [CustID] : MAX(DATETRUNC('month',[Createdate]))}) =< 6 THEN '0 - 6 months'

          ELSEIF DATEDIFF('month', [date], {FIXED [CustID] : MAX(DATETRUNC('month',[Createdate]))}) > 7 AND DATEDIFF('month', [date], {FIXED [CustID] : MAX(DATETRUNC('month',[Createdate]))}) =< 12 THEN '7 - 12 months'

          ELSEIF DATEDIFF('month', [date], {FIXED [CustID] : MAX(DATETRUNC('month',[Createdate]))}) > 13 AND DATEDIFF('month', [date], {FIXED [CustID] : MAX(DATETRUNC('month',[Createdate]))}) =< 18 THEN '13 - 18 months'

          ELSEIF DATEDIFF('month', [date], {FIXED [CustID] : MAX(DATETRUNC('month',[Createdate]))}) > 19 THEN '19+ months'

          END

          • 2. Re: Month Range Buckets Using Date Field
            Leanne Vermeulen

            That calculation fills more than one bucket per Customer ID, instead of just one:

             

            Screen Shot 2017-09-26 at 8.56.42 PM.png

            • 3. Re: Month Range Buckets Using Date Field
              Jennifer VonHagel

              Hi Leanne, I think the problem is that you're just grabbing the month of Customer's most recent purchase, rather than a proper date with year so that it can subtract over multiple years.

               

              We could effectively grab the month and year of a customer's last purchase by doing datetrunc('month',[createdate]), which would return 9/1/2017, 8/1/2017, etc.

               

              But Tableau's DATEDIFF() calculation doesn't even require that we get the month, it will take care of that for us if we just give the exact date.

               

              I created this formula from Sample Superstore data to get a Customer's most recent order date.

              "Cust Last Order Date": { FIXED [Customer Name] : MAX([Order Date]) }

               

              Then created this formula to find the number of months from the customer's last order date until today. After creating this calculation, I turned it from a measure into a dimension.

              "Months since Cust Last Order Date": { FIXED [Customer Name] : min(DATEDIFF('month',[Cust Last Order Date],TODAY())) }

               

              I can chart that like this:

              Then I simply created an IF statement to bucket the months as you indicated:

              "Months Bucket":

              IF [Months since Cust Last Order Date] >= 0 AND [Months since Cust Last Order Date] <= 6 THEN '0-6'

              ELSEIF [Months since Cust Last Order Date] >= 7 AND [Months since Cust Last Order Date] <= 12 THEN '7-12'

              ELSEIF [Months since Cust Last Order Date] >= 13 AND [Months since Cust Last Order Date] <= 18 THEN '13-18'

              ELSEIF [Months since Cust Last Order Date] >= 19 THEN '19+'

              END

               

              The workbook is attached.

               

              Hope this helps,

              Jennifer

              1 of 1 people found this helpful
              • 4. Re: Month Range Buckets Using Date Field
                Leanne Vermeulen

                Jennifer that is brilliant; thanks so much! That second calculation to determine datediff to current date is a genius approach

                • 5. Re: Month Range Buckets Using Date Field
                  Andrew Kim

                  Haha, I should have probably created my calculation more carefully.

                   

                  I also didn't even think about using TODAY().

                  • 7. Re: Month Range Buckets Using Date Field
                    Rajeswari S

                    Is there a way to make the above scenario work for showing static "Months Buckets" in the view. As of now, if we have values for that range eg: '0-6' it will display the bucket on the view otherwise it wont.  However, regardless of values I need to show the buckets in the view.

                     

                    Thanks & Regards,

                    Rajee