You are correct when you stated that data cannot exist in multiple buckets. Once a condition is met, that data is essentially 'spoken for'. The way I have done this in the past, is creating multiple calculated fields, one for today, one for last 7 days, etc...
Hi Wes and thanks for the quick reply.
I thought about that path, but was not sure how to setup a filter to show the three calculated fields as the options to choose from. I could add each one as its own filter, but that would cause issues if they do not unselect the other filter... I'm a little new to the Tableau so I have not encountered a situation where I had to try to build a filter outside of a single calculation.
Do you create another calculated field to allow for filtering in your scenario or do you have some actions setup to make sure they can only select one of the filters?
1 of 1 people found this helpful
I can think of two options.
1) Create 3 calculated fields and show them all. Quick filters will continue to work.
2) Create a parameter and allow the user to select one of the time dimensions.
I'm working on a example workbook and will post it shortly.
1 of 1 people found this helpful
My suggestion would be to use a parameter and a calculated field. The parameter would be a string - list that would contain the values you mentioned.
Then create a calculated field that will determine if the record is in the category selected by the parameter, maybe something like this:
WHEN "Today" THEN
IIF(DATEDIFF("day",[CreatedDate],NOW()) < 1,1,0)
WHEN "Last 7 Days" THEN
IIF(DATEDIFF("day",[CreatedDate],NOW()) < 7,1,0)
WHEN "Last 30 Days" THEN
IIF(DATEDIFF("day",[CreatedDate],NOW()) < 30,1,0)
WHEN "Older" THEN
IIF(DATEDIFF("day",[CreatedDate],NOW()) >= 30,1,0)
This will create a calculated column that will have a value of either 1 or 0 depending on the age of the record, and the parameter value selected. Be sure to convert the "ShowRecord" field to a Dimension rather than a Measure.
Then, You place the "ShowRecord" field on your filter shelf, and select the "1" value only.
And then show the parameter controls that will allow you to change the value of the parameter and the records in your viz should change accordingly.
Let us know if that addresses your objective.
Since the Superstore data is not current I replaced Last 7 Days and 30 Days with calculations for 2013 and 2014. Same concept, I just expanded the ranges to work with the sample data. I think example number 2 is what you are looking for.
Attached you'll see two views:
1) Quick Filter Example with 2 Calc Fields
The calculated fields are named WR - 2013 an WR - 2014. As you will see, I added the Order Date quick filter and Region quick filter. Each calculated field still honors the quick filters selections.
2) Parameter Example
For this one I created a parameter and named it WR - Select Time Dimension with the following list of values.
I then created a calculated field named WR - Profit (Using Parameter) and placed it on the filters shelf with a value of 'T'. This checks that the parameter value matches and that the order date equals a specified date or range. If both the conditions are met then the field = 'T' and the corresponding Profit record will display.
if [WR - Select Time Dimension] = 'Today'
and [Order Date] = TODAY()
ELSEIF [WR - Select Time Dimension] = 'Year 2013'
and [Order Date] >= DATE("2013-01-01")
and [Order Date] < DATE("2014-01-01")
ELSEIF [WR - Select Time Dimension] = 'Year 2014'
and [Order Date] >= DATE("2014-01-01")
and [Order Date] < DATE("2015-01-01")
ELSEIF [WR - Select Time Dimension] = 'All'
else 'F' end
You'll noticed that if you select Today within the parameter, the view is blank. That is because there are NO records with an order date of today.
Take a look at the workbook and let me know if it's the correct answer or helpful or you have additional questions.
That's fantastic, works like a charm - thanks so much!