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

# Rolling Sum

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

Hi,

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

Mahfooj

• ###### 2. Re: Rolling Sum

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

Hi,

follow the below screen shots.

Mahfooj

• ###### 4. Re: Rolling Sum

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

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

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

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

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

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

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

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

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

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

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