1 2 Previous Next 17 Replies Latest reply on Dec 10, 2018 9:05 AM by Richard Roberts

    Aggregate Weeks over latest year

    Richard Roberts

      Hi all.   I have tried searching the forum and can not figure out how to aggregate weekly data into custom periods.   I have data at the weekly level and what I want to be able to do is show a chart with data ending every 13 weeks over the last two years.   Refer to the graph pictured below (this is from Excel).  I assume I need a calculated field but I'm not sure what calcs I need to do.

       

      Thanks in advance.

       

       

        • 1. Re: Aggregate Weeks over latest year
          Joe Oppelt

          There are a bunch of "it-depends" that can come into play here.

           

          Are you consolidating those 13-week chunks into standard calendar quarters?  (Jan-Mar, Apr-Jun, Jul-Sep, Oct-Dec)?

           

          If so, then make a calc like this:

           

          DATETRUNC('quarter', [My Date])

           

          The result of the calc is that each value will be the first day of the respective quarter.  (Feb 22 will result in Jan 1.  Mar 2 will result in Jan 1.  Aug 5 will result in Jul 1.  Etc.)  Run your chart along that calc, and you will get quarterly consolidations.

           

          This will result in dates on you axis of Jan 1, Apr 1, Jul 1, Oct 1.  If you want your dates to be the LAST day of the quarter, we can do that, but let's cross that bridge if you need it.

           

          Even simpler would be to drag the date field onto the columns shelf (or rows), and it will initially go on as YEAR([My Date]).  Expand that to QUARTER([My Date]).  You will have quarterly rollups from that.

          • 2. Re: Aggregate Weeks over latest year
            Richard Roberts

            Joe,  thanks for the reply.   I hadn't thought of the variables here.   To help clarify the quarters are not standard quarters but rather latest 13 weeks, previous 13 weeks, next previous 13 weeks, etc.   I would want the dates to be the last date of the data for each of the 13 week blocks. 

             

            I use this calculated field formula to get the latest 13 weeks,  what I don't know how to do is get the 13 week blocks further back.

             

            DATEDIFF('week', DATETRUNC('week', [PERIOD_END_DATE]), DATETRUNC('week', {FIXED : MAX([PERIOD_END_DATE]) } )) < 13

            • 3. Re: Aggregate Weeks over latest year
              Joe Oppelt

              What defines the start- and end-date of a 13-week period?

               

              If it's the current week on your chart, are you looking for current week plus prior 12 weeks?  Same operation from week to week along the chart?

               

              Do you want something to indicate that a calculation along the chart doesn't have enough weeks to do 13 weeks?  For instance, if your chart has 3 years of data, what do you want to show on week-3 of the first year, since there isn't 13 weeks of data to aggregate?

               

              There is a calc that will let you sum up positional ranges.


              WINDOW_SUM(SUM(sales), -12, 0)

               

              This will sum up the current week plus the prior 12.

               

              WINDOW_SUM(SUM(sales), -25,-13)

               

              This will sum up the 13 weeks before the current 13 weeks.

               

              The label for the mark is window dressing.  First let's figure out what you want to do mathematically here.

              • 4. Re: Aggregate Weeks over latest year
                Richard Roberts

                I'm a bit of a novice here.  I got the Window_Sum function to work and it gives me exactly what I want as far the totals but I don't need the full table.  I'd like to only have the latest date with the totals for the 13 week, previous thirteen, etc.  

                 

                Here is what i got from the Window_Sum.

                 

                • 5. Re: Aggregate Weeks over latest year
                  Joe Oppelt

                  OK, that's a great start. 

                   

                  I notice that your window sums are doing TABLE(down).  Did you set that specifically?  Or did Tableau's default do that for you.

                   

                  The reason I ask is because I'm going to show you how to display only the last row.  You need to have the full table on the sheet so that Tableau can sum up all the historical weeks.  You can't just filter out all those old rows and leave the last one.  And what you're going to need to do is create a table calc as a filter to display that last row.  When you use a table calc as a filter, it won't delete out the rows that don't satisfy the filter.  It just controls the rows that get displayed from the underlying table.

                   

                  If you're not familiar with setting table calc addressing, hack up a simple packaged workbook and upload it here.  I'll show you.

                   

                  Video demonstrates how to anonymize your workbook/data

                  • 6. Re: Aggregate Weeks over latest year
                    Richard Roberts

                    Tableau is doing the sums down automatically.

                     

                    I built the same workbook using the sample superstore data.

                    • 7. Re: Aggregate Weeks over latest year
                      Joe Oppelt

                      Request:

                       

                      Do FILE -> EXPORT PACKAGED WORKBOOK on that.  This will create a TWBX file instead of a TWB.  Then I can open it.

                      • 8. Re: Aggregate Weeks over latest year
                        Richard Roberts

                        Argh... told you I was a bit of a novice.  Trying that again.

                        • 9. Re: Aggregate Weeks over latest year
                          Joe Oppelt

                          OK, this wasn't as complicated as I thought it might be.

                           

                          In the attached, I created a field called [index] and put it on the sheet.  The default of TABLE(down) suffices here.  (I'll tell you a bit about setting table calcs later.  It's a very useful thing to know.)

                           

                          You can see that INDEX gives me a 1-through-N count of the rows.  That's what I was aiming for.  The index() function is also a table calc, and I'm going to use that to get the last row.


                          On Sheet 1(2) I made another calc where I get [index] = window_max([index]).

                           

                          window_max is a table calc.  It says for all rows find the max of [index].  (I can't just do MAX(index) because index is a table calc, so I need to use a table calc to find a subsequent examination of all the occurrences of the table calc.  So I use window_max here.)


                          You can see on Sheet 1(2) that now we just see the last row.  The whole rest of the table is still underlying the sheet.  But the table calc filter only allows one row to display here.

                           

                          As for setting table calc settings, right click on a table calc pill and select "Edit Table Calc".  You get a pop up that looks like this:

                           

                           

                          In this screen shot we see that Table(down) is selected.  (That was tableau's default.)

                           

                          On sheet 1(3) I added YEAR of ship date to the sheet, and I modified the table calc for [index] to restart every year.

                           

                          This is what the setting looks like there:

                           

                           

                          And you can see that we now get 1-through-N for each year.  That's how I saved the workbook I uploaded here.

                           

                          For kicks, change it to look like this:

                           

                          (Select Deepest for Restart, and then select Year for "at the level".)

                           

                           

                           

                          This setting gives you a 1-through-N count of the years in the table.

                           

                          This little exercise just gets you going in the world of table calcs.  Your next sheet may not be laid out in a way that tableau's default works for you.  Messing with table calc settings forces tableau to "walk" the table in the order you want it to.

                          • 10. Re: Aggregate Weeks over latest year
                            Richard Roberts

                            I really appreciate the help and your time.  Can you attach the workbook?  I might have missed it.

                            • 11. Re: Aggregate Weeks over latest year
                              Richard Roberts

                              No need to attach.  I followed the instructions and got the right answer!   One final question,  how I do change the field labels to the max date for each period.  In other words I want the label to read "13 weeks ending MM/DD/YY"?

                               

                              • 12. Re: Aggregate Weeks over latest year
                                Joe Oppelt

                                My bad on not attaching.

                                 

                                Attached has all the previous stuff.  Also on sheet 1 I created some calcs.  First I made a [week starting] calc.  It uses DATETRUNC().  This function changes the input date to the beginning of the specified datepart.  So in the calc I'm truncating to week.  There are a bunch of rows in each week for this sheet, of course.  The calc does this for all rows.  March 4 through March 10 all get changed to March 4.  Beginning of week.  Then I did [Week Ending].  I nested the datetrunc inside a DATEADD function.  This adds (or subtracts if you give a negative number) the specified number of the specified "date parts" to the input date.  My input date is the result of the DATETRUNC operation. (We can nest these things like I did in there.)  In this case I can just add 6 days to get the end of the week.  (Note:  All these calcs are wrapped in the DATE() function.  By default Tableau pumps out a date-time value, so DATE() truncates the output to just the date portion.)

                                 

                                But there is more power in these functions, so for demonstration purposes I made a [Month Ending] calc.  This is where things can get really cool.  Edit [Month Ending].

                                 

                                DATE(DATEADD('month',1, DATETRUNC('month', [Ship Date])))-1

                                 

                                Here I first take each row's date and truncate it to the beginning of its month.  Then I add a month.  The -1 at the end subtracts a day.  (I could have done that with a dateadd as well, but tableau's date math works at the day level here, and -1 subtracts one day -- no matter what datepart you're messing with.)

                                 

                                So interpreting that calc:  If my input is June 15, the datetrunc changes the value to June 1.  The dateadd changes the value to July 1.  The -1 subtracts a day to June 30.

                                 

                                This is valuable because not all months end in 30.  Tableau gets the right last-day-of-the-month this way.  Even on leap years for February.

                                 

                                But notice in that column some of the weeks have two end-of-month values!  December 31 week has days in December and days in January.  I didn't account for that.  So the Jan 2 rows dutifully truncated to Jan 1 because the calc didn't care about what week it fell in.

                                 

                                So look at [Month Ending 2].  Here I first truncated to the week (which moved Jan 2 to Dec 31) and THEN truncated to the month, and did all the rest the same.  And you can see that I get 12/31/17 for all rows within the week of Dec 31.

                                 

                                So that takes care of making the right value to display for end of week.  (Plus some extra demonstration tossed in.  )

                                 

                                But you say you want the column label to display this value.  We don't have a way of dynamically naming the label.  We can only control the data like this.  Looking at Sheet 2, what did you actually hope to see?  I might be able to hack something to accommodate your need.

                                • 13. Re: Aggregate Weeks over latest year
                                  Richard Roberts

                                  What it looks like happens is that each date bucket shows ending date of the final row on sheet 2.   What I would like to see is that for the prior the ending date is 9/8 (the ending date for the prior 13 weeks) and so for the prior2 and prior3.  

                                   

                                  • 14. Re: Aggregate Weeks over latest year
                                    Joe Oppelt

                                    Well, yeah.  We can make those period-ending calcs for all the older 13-week periods.  The question I have is where you hope to see them.  That's the hard part, and your expectation of where they'll get displayed will determine what we need to do to display them there.

                                     

                                    PS:  We're not going to get them into the tooltip for each different measure.  The values in a tooltip are determined by the dimensions that create the mark.  The list of measures that exist for the last week are all determined by the week being displayed, and the displayed values in the individual tooltips will be uniform across all the measures.  (Except for the specific Measure_name and Measure_value, which are specialized items Tableau displays and we cannot change.)

                                     

                                    And we cannot change the actual name of the measure fields dynamically.

                                     

                                    What I have in mind is to create the particular strings for "Current sales, ending xx/xx/xx", "Prior 13 weeks ending YY/YY/YY", etc.  and then overlay a second sheet on a dashboard to be the "labels" for your columns.

                                    1 2 Previous Next