2 Replies Latest reply on Jun 29, 2015 3:40 PM by Thorsten Vogt

# Effect of filtering on first days of "rolling" n-day table calculations?

For non-proprietary illustration purposes, let's say I was a waiter who wanted to calculate my rolling 7-day tip rate.

Using Tableau, I can (suprisingly easily!) create rolling 7-day tip amount and bill amount totals, and divide them to create a rolling 7-day tip rate (tip amt/bill amt).

What I'm running into is an issue when I try to limit the display by filtering for a particular month. What seems to be happening is that the filter for one month, say July 2011, eliminates the entries from the prior month (June 2011) that are needed to compute the rolling 7-day tip rate for the first 6 days of the current month (July 2011). As such, the curve for the first 6 days of the month are not "smoothed" as they could be.

Is there a clever trick to get around this (filter on one range, compute on another) or a different way of accomplishing this?

Thanks!

• ###### 1. Re: Effect of filtering on first days of "rolling" n-day table calculations?

Here's the trick: Create a table calculation that generates the desired range, then put that on the Filter shelf. Tableau evaluates table calculation filters after all other filters and calculations (with the exception of reference lines), so the chosen rows can be effectively hidden.

Attached is a sample workbook.

1 of 1 people found this helpful
• ###### 2. Re: Effect of filtering on first days of "rolling" n-day table calculations?

Thanks, this seems to work not only for dates, but also for dimensional text fields, such as State.

LOOKUP(MIN([State]),0) will create a list of States that can then be used for filtering, without affecting the total for other calculations such as Percentage of Table .
Since MIN() of a text field does not really makes sense, am I correct in assuming the only goal of this calculated field is creating any table calculation, even though (or maybe precisely because) it does not actually do anything?

Also, I apologize for the post necromancy, but I found this to be a common problem, and that trick is super helpful.