How to create a custom date filter in Tableau?
In Tableau, I have a timestamp field named "DAY" and I wished to create a custom date whose data type is date & time like "30/06/2024 13:00:00". So first I created a parameter in which I set the Data Type to string and chose three allowable values "Hour", "Day" and "Week". Second I created a calculated field named "Custom Date" in which I used the code below: CASE [Date_Parameter] WHEN "Hour" THEN DATETRUNC('hour', [DAY]) WHEN "Day" THEN DATETRUNC('day', [DAY]) WHEN "Week" THEN DATETRUNC('week', [DAY]) END So the parameter creates three buttons: "Hour", "Day", and "Week" that allows me to change the granularity for a graph. I dragged the Custom Date field to the columns shelf and a measure to the rows shelf. And then I used the built-in function "Exact Date" on the Custom Date field. This solution works fine for every mark except for the bar mark that displays a histogram. When I try to display a histogram with the calculated field Custom Date and set the the field to Exact Date the bin size of each bar does not change as intended. When I click on "Hour" button, the bin sizes seem to be large and supersposed on top of each other. When I click on "Week" button" the width of the bar shrinks, that's because the "DATETRUNC('week', [DAY])" in the calculated field executes but it is influenced by the built-in function "Exact Date" and shrinks the bars width as a result. What I want to have is the same result when I use the built-in functions "Hour", Day (May 8, 2015)" and "Week Number (Week 5, 2015)". Is there a workaround solution to this?
2 answers ( 0 marked as helpful)
Hi Sofiane,
To get the histogram working the way you want, you'll want to skip using the "Exact Date" on your "Custom Date" field. Instead, try creating separate calculated fields for each granularity—Hour, Day, and Week—and use your parameter to switch between them. Then, drag the right field to the columns shelf based on your selection. This way, you’ll keep your bin sizes consistent and avoid the strange bar width changes.
Hope this helps!
Best,
Ned
Hi Ned,
Thank you for your quick answer.
I followed these steps but it still doesn't work:
- I created a calculated field for each granularity
"Hour Field" | Code: IF [Date Parameter] = "Hour"
THEN DATETRUNC('hour', [Invoice Date])
END
END
"Day Field" | Code: IF [Date Parameter] = "Day"
THEN DATETRUNC('day', [Invoice Date])
END
THEN DATETRUNC('day', [Invoice Date])
END
"Week Field" | Code: IF [Date Parameter] = "Week"
THEN DATETRUNC('week', [Invoice Date])
END
THEN DATETRUNC('week', [Invoice Date])
END
Then, I created another calculated field I named "Custom Date" to switch between granularities. The code is:
CASE [Date Parameter]
WHEN "Hour" THEN [Hour Field]
WHEN "Day" THEN [Day Field]
WHEN "Week" THEN [Week Field]
END
WHEN "Hour" THEN [Hour Field]
WHEN "Day" THEN [Day Field]
WHEN "Week" THEN [Week Field]
END
The result is pretty much the same. There is no way to skip the builtin functions: "Exact Date", "Day", "Hour" ....
Thank you!
Thank you!
Best Regards,
Sofiane