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

    Understanding the best way to group dates to sum values

    Richard Khuu

      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:

      Week 53Week1Week 2
      GroupDecember 31,2012January 1,2013January 6,2013Totals

      Test 1

      6261345
      Test 23352058
      Test 31042456
      Grand Totals1910337159

       

      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
          Shawn Wallwork

          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:

           

          GroupWeekValue
          Test 1Week 536
          Test 1Week126
          Test 1Week 213
          Test 2Week 533
          Test 2Week135
          Test 2Week 220
          Test 3Week 5310
          Test 3Week142
          Test 3Week 24

           

          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:

           

          Weeks Group.PNG

           

          --Shawn

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

            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.

             

            Thanks for your time.

             

            Best Regards,

            Arthi.

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

              Hello,

               

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

              Can you please help me get the Date Groups..

               

              Regards,

              Arthi.