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

# Month Range Buckets Using Date Field

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

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

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

• ###### 3. Re: Month Range Buckets Using Date Field

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

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

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

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

• ###### 6. Re: Month Range Buckets Using Date Field

• ###### 7. Re: Month Range Buckets Using Date Field

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