Yes, you are missing something--but its not your fault, it comes with Tableau experience. And yes, it should be much simpler--or at least much more discoverable to new users.
Date filters will be applied earlier in Tableau's order of operations than the Table Calcs. Tableau sends queries and tries to do as much work as possible all at once, but the order in which things are carried out is not visually available to us. So, other users have spent painstaking hours studying log files and Tableau behavior to come up with their own understanding of this order of operations. Here's a helpful link that I refer to, in terms of this order of operations:
Note that this thread is a little old, but it is still a great reference--Joe Mako's comments are what you want to focus on, in terms of understanding (at least part of) the order of operations in Tableau.
I hope this helps. If you can post a sample workbook, I'll take a look and see if I can come up with any solution for you. You could mock up an example using Superstore Sales, or randomize a subset of your own records, and use that as your data source in Tableau. If you need help creating the sample data, here are some guidelines from yet another Tableau guru (comment from Jonathan Drummey is what to focus on here):
Thanks Matthew, here's a similar situation using Superstore_Sales.
To reiterate the above, here's what I'm trying to accomplish: I have a couple of months worth of sales, and then I estimate sales moving forward ([Sales Forecast]). My problem is that when a user filters by date, the estimation will only be based on those dates.
Superstore w Forecast.twbx 1.1 MB
Right.. So, the date filter is applied BEFORE the Table Calc, so your dates get removed.
To make these align, we can make the Order Date a Table Calc with a LOOKUP calc. LOOKUP returns the value of the given expression in a target row, specified as a relative offset from the current row. So, we use an offset of 0. Because all Table Calcs must be aggregates, we wrap the Date field in MIN (we could use MAX, ATTR, or any other Aggregation type--they'll all be equivalent in this case).
So, I converted Order Date (for your filter ONLY) to be:
LOOKUP(MIN([Order Date]),0) -- Make this a Continuous (green pill) field.
Workbook attached. Let me know if this is what you're after, and if my logic has made sense to you. Note that we kept WEEK(Order Date) in its original form, so that is computed before the Table Calcs in Tableau's pipeline of operations.
I hope this helps, AND makes sense. Cheers!
1 of 1 people found this helpful
Here is another post on this topic, just for reinforcement--this technique is useful in a number of situations and does not only apply to dates. The second link is an example using a discrete dimension, like Category:
This was one of the very first "Tricks" I ever learned in Tableau. I am hopeful it will excite you, not discourage you. Yes, it could be easier--but learning some of these tricks will enable you to use Tableau in more interesting and exciting ways!