1 2 3 Previous Next 30 Replies Latest reply on Aug 15, 2018 9:29 PM by Aaron Mak

# Cumulative sums over time combined with a date filter

Given a data source with a date field and a numeric value field. I know how to use tableau's advanced table calculations to calculate the cumulative sum of the value over time.

Now however, I create a worksheet and add a filter to filter the worksheet by a date range. The problem is that the cumulative sum is reset to zero based on the left-most date in the *filter's* date range. I want the cumulative sum to always start at zero from the earliest date in the datasource itself, even if the worksheet is filtered by a different date range.

Anyone have a suggestion?

• ###### 1. Re: Cumulative sums over time combined with a date filter

As Joe Mako has mentioned/shown a couple of times, if you duplicate the data source and then edit the relationship table so only one irrelevant field does the linking, then you can use the copy to get around the filter. Tomorrow I'll try to dig up Joe's original post on this, he explains it a lot better than I just did. --Shawn

1 of 1 people found this helpful
• ###### 2. Re: Cumulative sums over time combined with a date filter

A trick which often works in cases like this is to define a calculated field which uses the LOOKUP() table calculation function to return the value from the current row, and then define a filter on that.  Filters on table calculations are performed after all calculations have been evaluated.

So define:

[Filter Date] = LOOKUP(ATTR([Date]), 0)

and filter on that and your running sum should start from the beginning as you want.

2 of 2 people found this helpful
• ###### 3. Re: Cumulative sums over time combined with a date filter

Joe & Richard thanks for the lesson. --Shawn

• ###### 4. Re: Cumulative sums over time combined with a date filter

Shawn, because we are looking for a RUNNING_SUM() and not a TOTAL(), I don't see how a self data blend would work for this situation.

I think the attached may be one option, similar to Richard's, but with a parameter based date range selection.

2 of 2 people found this helpful
• ###### 5. Re: Cumulative sums over time combined with a date filter

Thanks for the help, folks. The solution using LOOKUP handles the cumulative sum properly, however, the filter on [Filter Date] is not a date filter, but is a filter for a discrete list. Is there a way to use regular date filter (range of dates, for example). Picking individual days from a long list is awkward. The example provided by Joe using parameters provides a reasonable filter widget, though. However, the "Date Range Filter" is defined as (LOOKUP(ATTR([Date]),0)<=[From Date] AND LOOKUP(ATTR([Date]),0)>=[To Date])

OR

(LOOKUP(ATTR([Date]),0)>=[From Date] AND LOOKUP(ATTR([Date]),0)<=[To Date]). I believe, however, that the first clause of the filter, namely  (LOOKUP(ATTR([Date]),0)<=[From Date] AND LOOKUP(ATTR([Date]),0)>=[To Date]) is always false and can be eliminated. Or am I missing something.

Thanks again for the help. This issue has stumped me for quite a while.

2 of 2 people found this helpful
• ###### 6. Re: Cumulative sums over time combined with a date filter

If you just do the calculated field using LOOKUP() as I suggested and then make that a continuous date it does what you want - as attached.

The reason I can see for using Joe's parameter approach would be if you want to have the from and to dates available within other calculated fields.

I think the reason for the extra complexity in Joe's calculation is because you have to have two independent sliders which can slide past each other.  So Joe's calculation is saying "the dates between these two sliders", allowing for the case where the sliders are the wrong way around.

1 of 1 people found this helpful
• ###### 7. Re: Cumulative sums over time combined with a date filter

Richard,

Thanks, you're right. This solution is much simpler and does exactly what I want.

Thanks!

• ###### 8. Re: Cumulative sums over time combined with a date filter

I've updated Joe's workbook to include another tab, "Simpler Solution" that illustrates Richard's approach.

1 of 1 people found this helpful
• ###### 9. Re: Cumulative sums over time combined with a date filter

This is a great thread - it's good to see people discussing various methods to achieve the same goal, and then dissect why different approaches might succeed.

Andy

2 of 2 people found this helpful
• ###### 10. Re: Cumulative sums over time combined with a date filter

Great thread indeed, I had just put my first running sum report into production and saw the need to correct the baseline value at the beginning of the filtered date range - the Simpler Solution above was perfect.  Thank you all.

P.S. Hey Andy, when are you going to get one of those cool TABLEAU TEAM tags next to your forum handle?

1 of 1 people found this helpful
• ###### 11. Re: Cumulative sums over time combined with a date filter

Hi Zach

Hmmm- I had a "Tableau Team" badge last week....

We've made some behind-the-scenes changes since then so I expect that's got something to do with it. Not to worry, there are more changes on the way, so we'll all see lots of different things soon!

Andy

1 of 1 people found this helpful
• ###### 12. Re: Cumulative sums over time combined with a date filter

Richard,

When using the LOOKUP(ATTR([Date]),0) technique as a "table filter" is there any way to find out the minimum and maximum values of the filtered date range, or, better yet, offsets or indexes to first and last rows that are shown? I'd like to be able to perform some secondary calculations on the filtered data. I've been trying flavors of INDEX(), FIRST(), LAST(), PREVIOUS_VALUE(), etc. and have had no luck.

Jonathan

1 of 1 people found this helpful
• ###### 13. Re: Cumulative sums over time combined with a date filter

Hello Richard and Jonathan,

We've been using the methods described in this thread for quite a while, they work beautifully, but I'm returning because I happen to have a similar need as Jonathan, I'd like to be able to include the table filter setting in the view title, and the best I've been able to do is use the <Date> field which resolves in titles to be all dates shown in the view. This is almost enough for me, but what I really want is the title to say "Utilization History for last X days (date range) with Y-day moving average."  I've got the date range and Y-day part working, but I can't see how to get X, or to use a parameter to set X which then affects the table filter.  In this example, X should be 90.

Regards,

Zach

1 of 1 people found this helpful
• ###### 14. Re: Cumulative sums over time combined with a date filter

Hi Zach,

You caught me at the right time, I just got on the forums...I don't know of any way to get Tableau to dynamically pick up the settings of filters on table calculations, so a parameter-based solution is probably your best bet. I set up one in the attached. It was a little tricky because I kept on getting a "TODAY() is not defined in the current context" error, using the parameter within a calculated field and then using that result in the date filter worked.

Jonathan

1 of 1 people found this helpful
1 2 3 Previous Next