How the EXCEL do I Even Begin to Calculate that in Tableau? A Beginner’s Guide to Creating Complex Calculations

I recently did a webinar where I showed an automated holiday calculation in Tableau.  A LOT of people told me they wouldn’t even know where to begin.

The Matrix

When I was figuring out Tableau, my go-to for everything was Michael Cristiani.  He deserves a medal for all the emails, LinkedIn messages, DMs, and phone calls he fielded from me (why only choose one method, when you can use them ALL?).  If you are in the Tableau community and don’t know him, you should.  Not only is he wise in all things Tableau, he’s one of the nicest human beings I know.  Follow him, buy him raw vegan food (ask him about this), and cheer at him at the conference.  Simply put, he rocks.

Here’s what I slowly learned about calculations: they’re a lot like any problem.  Like puzzles, chess, and even business problems, they all get solved piece by piece.  So, I went to the tried and true solution of life: sticky notes.

I started going through the pieces of the problem and figuring out their intricacies.  Slowly, I’d come up with a framework.

If I was doing holidays, the process might look like this:

1. Start with the holidays we care about – for me, this usually meant hitting Google for when these holidays occurred, because I have no clue about holidays, despite my mother working for a Hallmark store.

2. Figure out their rules.

Here’s where we stand right now:

Stickies! The OTHER answer to life.

3. I organize, so I can start understanding what I need to do.  For this, I chose complexity for organizing.

They get worse as you go right.

4. I start figuring out what it is I need to do.  Now, there’s lots of ways to do this stuff and people out there who are LOADS smarter with calculations.  They usually live on the forums and are absolute gurus.  My focus is process, not stellar calculations.  If you want stellar calculations, visit Jonathan Drummey, Shawn Wallwork, Joe Mako, Pooja Gandhi, and more (If I forgot you, it’s not because I forgot; it’s because I don’t make lists).

Fixed month and day were the easiest.  A couple dateparts and I’m on my way.  First or last involves a Level of Detail calculation.  I need it at the month-year level (not row – since row could be any date).  By choosing either the min (first Monday) or the max (last Monday) of the month-year, I can fully automate these holidays too.  HOORAY!  HOORAY FOR LODs!

This is where I bust out my old C++ program and start coding.  It sure looks like it, but I swear, it’s 100% Tableau.  Just like before, I need to focus on Thursday.  I could’ve done this several ways, but chose my version because I don’t always have transactions on every single day, which means some ways (such as a dateadd on a LOD similar to my Labor Day calc) wouldn’t work.

My method was to count weeks within the month, then filter for Thursdays, then offset if the first of the month occurred after Friday.  This way, I don’t depend on my data as much (I still need at least 1 transaction in a month).

Now, that I know what I’m going to do, I hit Tableau with a roadmap for this series of calculations.

1. First up – custom date: for this I simply right-click, select Create, then Custom Date.  I go with Month & Year as a datepart. For clarity, I name this _0 Order Date, so I can find it and others can follow my logic.
2. First of the Month – this sets every month down to the first.  There’s several different ways, I chose ‘DATEADD(‘day’,-(day([Order Date]))+1,[Order Date])’
3. Week in Month – This resets the week number to 1 for every month.  It’s handy if you want to make a calendar go across for a year and it works great here.  ‘DATEDIFF(‘week’,[_1 First of the Month],[Order Date])+1′
4. 4th Thursday – Oh, Thanksgiving, how odd you are to land on Thursday.  How happy I am when I get Friday off to sleep, eat leftovers, and avoid all things shopping related.  Calc: ‘If DATEPART(‘weekday’,[_1 First of the Month])<=5
   THEN [_2 Week in Month]
   ELSE [_2 Week in Month]+1
   END’
5. Holiday – this, I’ll paste at the very end.  Or, you can just download the workbook, copy and paste all of these at once, and sub in your date field.

//And final disclaimer: I am not a calculation expert – there are loads of people who do this better!

A Cheat Sheet: So, how the EX**L do you even begin to calculate that in Tableau?

1. Bust out the sticky notes.  Colorful is best.
2. Brainstorm out what you need.  It does not (and most likely should not) have any type of order.  Just sticky the ideas.  4 words or less per sticky is ideal.
3. Organize what you have.  Consolidate, remove, summarize.
4. Start describing the “rules” of your calculation.  Each bucket should have a rule.
5. Reorganize if you see trends or feel the need.
6. Pseudo code if you want.  Some people find this helpful (I do!).
7. Make the calculations in Tableau.  Sometimes it helps to do them in bits to make sure they work.  Or, use // to comment out pieces.
8. Make views that confirm your calculations are working as intended.
9. Fix, organize, rename, and use a folder to make friends.
10. Have a party.

Or a dashboard.

And…if all else fails, use the Calculation Dartboard.

Made it this far?  Alright, scary holiday calc:

IF
//New Years Day
//Holiday is always 1/1
month([Order Date])=1 AND day([Order Date])=1
THEN ‘Holiday’
ELSEIF
//Memorial Day
//Holiday is LAST Monday of month
[Order Date]=
//Using Level of Detail Calculation to find last Monday
//logic looks at every month/year to find the max date
//calculation inside filters to ONLY May
//If you have year as a field, you can also use that instead
{FIXED [_0 Order Date (Month / Year)]:
//Similar to pivot, this sets the split (left side)
MAX(
//pulling only the MAX or largest date in May that’s a Monday
//calcs can be INSIDE parens
IF month([Order Date])=5 and
//finding May
datename(‘weekday’,[Order Date])=’Monday’
//picking out only Mondays
THEN [Order Date] END)}
//LOD is closed with }
THEN ‘Holiday’
ELSEIF
//Independance Day
//Holiday is always 7/4
month([Order Date])=7 AND day([Order Date])=4
THEN ‘Holiday’
ELSEIF
//Labor Day
//Holiday is first Monday of the month
[Order Date]={FIXED [_0 Order Date (Month / Year)]:MIN(
//using LOD again to find FIRST Monday
IF month([Order Date])=9 and
datename(‘weekday’,[Order Date])=’Monday’
THEN [Order Date] END)}
THEN ‘Holiday’
ELSEIF
//Thanksgiving
//4th Thursday of November
[Order Date]=(
IF month([Order Date])=11 and
[_3 4th Thursday]=4 and
datename(‘weekday’,[Order Date])=’Thursday’
THEN [Order Date] END)
THEN ‘Holiday’
ELSEIF
//Christmas
//Holiday is always 12/25
month([Order Date])=12 AND day([Order Date])=25
THEN ‘Holiday’
END