so KPI, so user friendly, so easy, so automated

Tableau Quick Win | Date buckets

Date buckets in Tableau Desktop, an easy method.

Welcome to my Tableau Quick Win series. Today, I’d like to feature Date buckets in Tableau Desktop. It’s a tool that crafts XML files on a users local computer. Tableau desktop is a business intelligence tool used to help people automate insights and discover things in their data.

I’m a lazy expert and enjoy making complex requests simple, automated, easy to share, easy to support and remediate because I get bored if I’m stuck supporting the same solutions.

Many years ago…
There was a large group of executives…
Who did not want a “between dates” as a date filter…
You know what I’m talking about, two calendar inputs.
They said “make it simple.”
And I said, “ok. happy to help.”

Below, please find my period over period automation to build self generating date buckets. It’s easy to share, wins without any hassle, and is flexible.

It only takes 3 steps to build self generating, automated, date buckets in tableau desktop.

lol, type this stuff or paste the code coming from this tutorial.

Below please find my quick win tutorial as a means of quickly winning… on any Tableau workbook with a date and a parameter.

We will be using the SuperStore Subset of data.

Which comes with every license of Tableau Desktop. In your data, you probably have a date. Use that date and follow along with these next two steps.

To begin, you need a date, and a parameter.

Step 1, make a date variable named W.

Create a new calculated field in tableau desktop, call it W.

make a simple variable W in place of your date. your date goes in this calculated field.

Now make the parameter.

Step 2, make a parameter variable named X. It’s an integer.

This will be the number of ‘X’ per period of analysis.

make a simple variable X in place of your parameter.

Paste the calculation below in any workbook with a Date and Parameter.

Above, if you followed along, you will not need to make any major changes to the calculation.

if 
DATETRUNC('month', [W])>
DATEADD('month',
-([X]+
datediff('month',{MAX([W])},today()))
, TODAY())
then "Current Period" //make this 0
elseif
DATETRUNC('month', [W])>
DATEADD('month',
-([X]*2+
datediff('month',{MAX([W])},today()))
, TODAY())
then "Previous Period" //make this a 1
else "Filter" //make this a 2
END
//[W] = date
//[X] = parameter

Drag drop this on to the view, right click filter, filter filter…

Now, only two buckets of time are available. You’re welcome!

Automated date buckets in Tableau

You just made automated date buckets in Tableau, and the end user can drive the visualization with the bucket generator. I prefer running this tool with days VS month, but month lets you add a nice “bucket” around dates coming in and out, and a day granularity offers a cool view to keep it simple for today.

So, it’s nice to have a quick date divider or BUCKET automation because it gives you the ability to visually show a difference between two date periods, or use the calculations for more logical flagging, subtracting from each other, finding differences without depending on the software to automate building it with window calculations.

hello & happy holidays from my family to yours. care to learn more? cool i got a bunch of extra smart stuff below this picture of my family.

Optimization to your date buckets in Tableau

You don’t need to continue. You don’t need to optimized this quick win solution for it to work and solve problems… Unless you want to know what else is possible, from an optimization perspective because you think everything can be optimized and want to know if I put in the due diligence beyond solving the problem above.

Optimization #1: remove LOD calculations

Nothing against LOD calcs, except they are slow and built to help users who don’t know SQL.

{max(W)} seeks to find the max date, you can find it easier using a subquery in your select statement. If you don’t know what that means, ask your data architect supporting your environment to add the max(date) as a column, and have it be repeated per row too. They will know what to do or you need a new data architect.

Optimization #2: stop using % difference or difference table calculations

Nothing against table calculations, except they are slow and built to help users who don’t know SQL.

Optimization #3: change strings to integers.

Nothing against strings, except they are slow and built to help users who don’t know integers are faster. Not entirely true but I don’t think you want me to explain what strings are for… Classification of data & not meant to aggregate unless you know how to chop chop chop the smart axe and want to pretend you’re going to throw sentiment analysis at me in this thought process and throw me off, but no, very diligent. That’s another blog…

It’s likely not your fault that you’re using strings in 2018 with if statements, it’s probably because someone taught you who also did not know how to write optimized Tableau calculations.

Optimization #4: ‘month’ date part… add a swapper.

The Datetrunc is used to round the dates to the nearest relative date part, that’s just how I explain it easily.

Date part can be a parameter.

DATEPART(date_part, date, [start_of_week])

NO I Don’t mean the Function Datepart.

DATETRUNC(date_part, date, [start_of_week])

YES I Mean Date_part, which is scattered in the calculation and easy enough to replace with a parameter full of date_parts. Now end user can play a bit more.

Optimization #5: remove max(date), add an end date parameter…

Remove {max(date)} or the subquery of max(date) explained above because you can give your end user the opportunity to change the end date. But tableau doesn’t let you update that value, which is a HUGE HUGE HUGE no no in eyes of anyone who cares about the analytics and NOT wasting time on clicking stuff. So, you can give them an end date to change the end of the bucket of time, but that is also another thing an end users now needs to start doing, where-as this follows you.

You made it this far? Still learning? Me too!!

Here’s the solution to the number one most requested product limitation @ tableau software. They call it “dynamic parameters”…

And I solved this with basic sql, so when I make jokes about you not knowing sql. Now you know it’s more directed to people who are missing out on making automated solutions which is technically only impacting their future revenue capability.

Typos by tyler garrett

if you like honest stuff, smart humor, pointing out how to make stuff more good, follow me on twitter.