4 Replies Latest reply on Oct 15, 2014 1:23 PM by Matt Lutton

# Running total with window

Hi!

I have a datasource where every row in the table is the delta of the customers. For example, if we have 10 new customers that date it's 10, if we have 10 new but lost 11 it's -1. I use a running total table calculation on this to show a graph of the number of customers over time.

The issue I'm having is that I'd like to show the graph for a specific time range, only the last year for example. Using a filter with last 12 months relative to today doesn't work because it will only show the difference over the last year, not the total (as all the data before that will get filtered out).

The only thing I found that's close to what I want is setting the axis range so it only show the last year, the issue with that is that it's not dynamic, so I'd have to change it every day to be accurate!

Is there a solution to this or have I reached the limits of what's possible with Tableau?

• ###### 1. Re: Running total with window

If I am understanding you properly, you need to remove dates from the view, but they still need to be available to your Computations.  You can do this by turning your Date dimension into a Table Calculation.  Because Table Calcs are evaluated after all the values are pulled into Tableau, this allows us to effectively "hide" dates while still being able to use them in calculations.  We typically do this by creating a Table Calc on our date dimension, such as:

LOOKUP(MIN([Date]),0)

Then, we place the regular discrete dimension pill for Date on the Detail shelf, and set the Compute Using on the Table Calc to be on the date dimension.

The basics of this idea are also shown here, although this one does not include Dates (but its the same concept/idea in practice): A Jedi (Filter and Table Calc) Trick | Tableau Software

• ###### 2. Re: Running total with window

Also, if you have trouble following any of the above, post a sample TWBX using the Superstore Sales dataset that ships with Tableau, and I'll be happy to try and provide my solution on that workbook. Cheers

• ###### 3. Re: Running total with window

That works perfectly, thanks a lot!

• ###### 4. Re: Running total with window

No problem; this comes up a LOT and there are a lot of useful scenarios for wrapping a dimension in a Table Calc like this.  Cheers