4 Replies Latest reply on Feb 13, 2012 8:43 PM by Andrew Fisher

# Running sum over period but only show last value

Hi all,

Not sure how to do this as the way I attempted to do it didn't work and I understand what tableau's attempting to do but don't know how to get around it or approach it differently.

So here's what I've got and what I'm trying to achieve.

For every day across a month I have a target value for number of orders. These are calculated out ahead of time. For each day as I move through the month we then get the number of orders (up to yesterday as this is done at 1am) that were made yesterday.

I can then show how we're tracking to our target by showing the target and the actual orders next to each other using running sums over the course of the month. That works fine.

In a cross tab then I can show what our current "drift" is - ie whether we are ahead or behind target by doing a running sum on the differences between actuals and target for each day. That all works fine too.

This all works perfectly at the month view without any problems.

What I'm trying to do though is produce a dashboard we can use internally that distills this down to just the number for where we are in the month to date.

EG:

date     1/1     2/1     3/1     4/1

orders     10     11     12     13

target     10     5          10     20

diff           0     6          2     -7

drift          0     6          8       1

So in my dashboard / worksheet then I'd have simply "Yesterday" as the date and it would show (from example above)

date     4/1

orders     13

target     20

diff          -7

drift          1

When I try and do this you can imagine what happens - because my partition only spans 1 day obviously my drift value simply shows -7 as that's what it is for the day in question.

So to my question then - how can I calculate a value like this which is a running sum of the differences over a period of time longer than the unit of time that I'm actually looking at - ie Month to date calculation?

Cheers

Andrew

• ###### 1. Re: Running sum over period but only show last value

You need a table calculation to test for the latest day and then define a filter on that.  Filters on table calculations get applied after everything else has happened.

I've pasted your data into an example workbook and defined this calculation to test for the last date:

ATTR([date]) == WINDOW_MAX(ATTR([date]))

Hmmm - I can't see any way to attach a workbook.

Just try creating a calculated field like that and then add a filter that only keeps True.

Message was edited by: Richard Leeke As I guessed, editing the comment let me add an attachment - I wonder why I couldn't initially.

• ###### 2. Re: Running sum over period but only show last value

Richard - if you click on the "Use advanced editor" link when creating a message it'll let you add an attachment the first time around.

• ###### 3. Re: Running sum over period but only show last value

OK, thanks.  I never would have guessed that adding an attachment was advanced!

• ###### 4. Re: Running sum over period but only show last value

Thanks Richard - that worked really well conceptually for what i was trying to do.

An additional complexity was that my target data was actually already stretched out for the entire month so the date filter alone didn't work so what I needed to do was create another filter that looked at whether the order data for each day was null or not. Once I had that the combination of calculating it to the last day we have order data for works a treat.

Cheers

Andrew