3 Replies Latest reply on Mar 5, 2013 11:04 AM by arthi.keat

# Understanding the best way to group dates to sum values

Hi,

I have a dataset that I am not sure what the best way to approach this in tableau. I am struggling trying to figure out how to group Work weeks that start at the end of one month and begin in another.

For example:

Group December 31,2012 January 1,2013 January 6,2013 Totals Week 53 Week1 Week 2 Test 1 6 26 13 45 Test 2 3 35 20 58 Test 3 10 42 4 56 Grand Totals 19 103 37 159

So what I would like to do in this example is to be able to Group Week 53 with Week 1 so that Week 1 is including the last day in December.

So for Test 1 for Week 1 we should see a total of 32, Test 2 is 38 and Test 3 is 52.

Thought I could use the group function in tableau but it was not available for the grouping my Date Dimension that I have formatted to Week Number.

Thanks,

Richard

• ###### 1. Re: Understanding the best way to group dates to sum values

Hi Richard,

First it is generally better to reshape your data so you don't have weeks running across the page. Here's what your data looks like after reshaping:

 Group Week Value Test 1 Week 53 6 Test 1 Week1 26 Test 1 Week 2 13 Test 2 Week 53 3 Test 2 Week1 35 Test 2 Week 2 20 Test 3 Week 53 10 Test 3 Week1 42 Test 3 Week 2 4

If you are working in Excel this reshaping is easy using a Andy's reshaper add-in. After you've got it reshaped then when you bring the table in the Weeks come in as a default text string. If you leave it as a text, then you can group it the way you wanted to:

--Shawn

• ###### 2. Re: Understanding the best way to group dates to sum values

Hi Shawn,

I needed your help regarding a related question to group the same data in a different way. Please let me know if you want me to start a new discussion.

What I wanted to do is to group data on the most recent week, a week prior and than all records greater than two weeks.

So, for this data:

Week2 would be the "Most Recent Week" and total Value would be 103

Week1 would be "1 Week Prior" and value would be 37

and last group would be "Greater than 2 weeks" and value would be 19

I wanted to do this dynamically and assume week groups based on the last date update in data rather than system date.

Can you please help me find a solution for this.. I have tried searching the forums but I could not find anything that could help me.

Best Regards,

Arthi.

• ###### 3. Re: Understanding the best way to group dates to sum values

Hello,

I searched archives but was not able to find a solution to my problem.