1 2 Previous Next 25 Replies Latest reply on Apr 11, 2018 10:34 AM by Nisha Mistry

    Rolling Sum

    Todd Moses

      I have a table of data with three columns:

      • Year
      • Month
      • Counter  (1 for all records)

       

      The data is loaded for December 2015 to February 2016.

       

      I want to create a report that shows me a running average of all of the count values:

      • January's Total would be the sum of the Counter field values where the Year is December of the Previous Year thru January of the Current Year.
      • February's Total would be the sum of the Counter field values where the Year is December of the Previous Year thru February of the Current Year.

       

      Is this possible?

        • 1. Re: Rolling Sum
          Mahfooj Khan

          Hi,

           

          You can do like this. It will be more helpful if you share the workbook.

           

           

          Mahfooj

          • 2. Re: Rolling Sum
            Todd Moses

            But how does the calculation know which months to include in the running total? ( If there is data from November 2015 to February 2016, I need to tell the calculation to start the running total with December 2015)

            • 3. Re: Rolling Sum
              Mahfooj Khan

              Hi,

               

              follow the below screen shots.

               

               

               

               

               

               

              Mahfooj

              • 4. Re: Rolling Sum
                Todd Moses

                This is great, but not quite what I'm trying to do.

                 

                For the purpose of the running total, the Year is actually 13 months, starting in December of the previous year, and going through December of the current year:

                 

                January 2015 = December 2014 + January 2015

                February 2015 = December 2014 + January 2015 + February 2015

                December 2015 = December 2014 + January 2015 + February 2015 + March 2015 + April 2015 + May 2015

                                              + June 2015 + July 2015 + August 2015 + September 2015 + October 2015

                                              + November 2015 + December 2015

                 

                So is there a way to tell Tableau to start/restart the running total in a specific month?

                • 5. Re: Rolling Sum
                  Mahfooj Khan

                  Hi Todd,

                   

                  Sorry for late reply. I've a workbook you can refer that to get the idea from that.

                  I hope this help you. In the workbook if you have to select month and year.

                   

                  Mahfooj

                  • 6. Re: Rolling Sum
                    Todd Moses

                    This is really close.

                     

                    I need the rolling 13 months to restart every January.

                     

                    January 2015 = December 2014 + January 2015

                    February 2015 = December 2014 + January 2015 + February 2015

                    December 2015 = December 2014 + January 2015 + February 2015 + March 2015 + April 2015 + May 2015

                                                  + June 2015 + July 2015 + August 2015 + September 2015 + October 2015

                                                  + November 2015 + December 2015

                    January 2016 = December 2015 + January 2016

                     

                    So is there a way to tell Tableau to start/restart the running total in a specific month?

                    • 7. Re: Rolling Sum
                      Joe Oppelt

                      You want to do a WINDOW_SUM of the previous 12 months plus the current one.

                       

                      WINDOW_SUM(sum([whatever]), -12,0)

                      • 8. Re: Rolling Sum
                        Todd Moses

                        NO.

                         

                        I want to a running sum. 

                        In January, the value would be the sum of January, plus the sum of the previous December. 

                        Then in February, the value would be the sum of February, plus the sum of January, plus the sum of the previous December.

                         

                        The running sum would start over again every January.

                        • 9. Re: Rolling Sum
                          Joe Oppelt

                          Ah.

                           


                          Do the running sum as described before.  You can tell table calcs to "Restart every..."

                           

                          Have it restart every year.

                          • 10. Re: Rolling Sum
                            Todd Moses

                            The solution that seems to be working for me is:

                             

                            IF AVG(DATEPART('month',[EffDate])) = 1

                            THEN WINDOW_SUM(SUM([Counter]),FIRST()-1,0)

                            ELSE WINDOW_SUM(SUM([Counter]),FIRST(),0)

                            end

                             

                            So far this appears to work with restarting every year of EffDate, as long as first value in the data set is the December month.

                            • 11. Re: Rolling Sum
                              Joe Oppelt

                              OK, that'll work.


                              And what happens if your first value in the data set is NOT December?

                               

                              You can test for the lowest value of [EffDate] this way:

                               

                              {fixed : min([EffDate]) }

                               

                               

                              That will look at all EffDates right up front and return the lowest in the data source.  (If you want the lowest in the sheet then you would do an INCLUDE of your dimensions instead of a FIXED with no dimensions.)  If your first one is not December, you can add in some logic to act accordingly.

                              • 12. Re: Rolling Sum
                                Todd Moses

                                The data will always start in December.

                                 

                                My solution above works when I'm only looking at one year.  When I'm looking at two years, it does not reset with the new EffDate year value.

                                • 13. Re: Rolling Sum
                                  Joe Oppelt

                                  If I had a workbook that modeled the sheet you're working on, I could show you how to reset that.  Otherwise, at best, I can only speculate based on what I think things look like.

                                   

                                  When you do EDIT TABLE CALC, you can do Table(across) and other settings.  One of them is ADVANCED.  Select that.  You'll have a list of all your dimensions in a box on the left side of the ADVANCED editor.  Depending on what things look like, you'll select some or all of the dimensions.  Make sure YEAR is at the top of the list of selected dimensions.


                                  When you exit the ADVANCED editor, there will be a pull down for "Restart every".  Select YEAR.

                                  • 14. Re: Rolling Sum
                                    Todd Moses

                                    I'm attaching the workbook.

                                     

                                    My whole point is that I'm trying to get January to be December plus January, February to be February plus January, and every January, start over again.

                                     

                                    Data will always start with December.

                                     

                                    Thank you.

                                    -Todd

                                    1 2 Previous Next