7 Replies Latest reply on Feb 25, 2019 12:08 PM by Russell Goldin

# Running Sum 12 Months With Prior 12 Months Data

I'm trying to put together a financial dashboard.  One of the requirements is to graph a cumulative 12 month revenue sum month over month.  Part of me thinks this is a windowed table calculation but I can't figure out how to just count the 12 months I need to add up.

Essentially for a specific month row, I have to show the cumulative prior 12 month sum of revenue.  My data set currently only has the gross revenue per month.

For example:

Nov 2012 will a running 12 gross profit sum of Dec 2011 to Nov 2012 to

.

.

.

October 2013 will have a running 12 sum of Nov 2012 to Oct 2013

How can I create this calculation?

• ###### 1. Re: Running Sum 12 Months With Prior 12 Months Data

If you can post a packaged workbook (.twbx), someone can provide an example solution for your data set.  There are several places to find examples of a Running table calc, in the meantime:

Running Total Calculation

Running Total Table Calculations | Tableau Software

These won't show how to set it up for the previous 12 months, though.  Someone can help you with that once a workbook is posted, though!

• ###### 2. Re: Re: Running Sum 12 Months With Prior 12 Months Data

Attached is the workbook.  When I use

WINDOW_SUM(SUM( [Gross Revenue] ),-11,0)

I get the correct results in a table format.  But I need to visualize that running 12 result and it doesn't work like so.

If it wasn't complicated enough, the visualization needs to just show the last 12 months.

• ###### 3. Re: Re: Running Sum 12 Months With Prior 12 Months Data

What kind of visual?

• ###### 4. Re: Re: Running Sum 12 Months With Prior 12 Months Data

In Sheet 2 of the workbook I attached represents the visual I need to show, but when I filter to the last 12 months, it makes the window_sum calculation results 'incorrect'

• ###### 5. Re: Re: Re: Running Sum 12 Months With Prior 12 Months Data

Abigail,

Your formula seems correct to me. The reason why the chart does not seem to be right is because it's showing annual data. I changed it to use a continuous date (month) dimension and it seems correct.

As far as showing the last 12 months goes, you need to create a filter that is based on a table calculation which is applied after the data is retrieved from the backend database.

There are different ways to write these filters, but I used the following:

[Date Filter] = lookup(datediff('month',attr([Fiscal Calendar]),today()),0)

Then, you select [Date Filter] <= 11 to get the last 12 months.

Is this what you are looking for?

Pedro

• ###### 6. Re: Re: Re: Running Sum 12 Months With Prior 12 Months Data

Pedro,

This solution worked perfectly! Thank you very much!

• ###### 7. Re: Re: Re: Running Sum 12 Months With Prior 12 Months Data

Here's another approach.