Where's the Money Go?

Topics covered: Calendar controls, Calendar visualizations


I've always been disappointed in Tableau for not having a built-in calendar view. Fortunately it's remarkably easy to construct one yourself using Tableau's wide array of date functions. All you need to get started is a data source with at least one date field.

As it turns out, this knowledge came in handy for a personal project I've been working on.  I recently moved into the city from the far-flung suburbs of Virginia, and have been noticing my wallet feels a little lighter than usual. Even taking into consideration the usual suspects (higher rents & grocery costs) it didn't seem like I should be seeing quite this much damage to my bottom line.

I decided what I really needed was two things:  A quick visual to let me identify days of above-average spending, and a breakdown of category spending by year and quarter.  Below is what I came up with using data from one of my credit cards (lowered by a random factor for privacy reasons).  If you want to learn how to set up your very own calendar control, read on after the jump.

Powered by Tableau

Below is the behind-the-scenes image of how the calendar control is set up.  Note that all of these fields are calculated off of the [Date] field contained in the underlying data (with the exception of [Select All], which is a fixed text string).

You can download the workbook yourself to take a look, but most of the formulas are so basic they're worth recreating here:

Columns:

[Year]: year([Date])
[Month Number]: datepart('month', [Date])
[Month Name]: datename('month', [Date]) + " " + datename('year', [Date])
[Day of Week]: datediff('day', datetrunc('week', [Date]), [Date])

Rows:

[Select All]: "Click Here to Select All"
[Week Number]: datepart('week', [Date]) - datepart('week', datetrunc('month', [Date]))

The trickiest part of this, by far, is the [Week Number] calculation.  Datepart('week', [Date]) returns the week of the year (1 thru 52, I believe), not the week of the month, so we need to force it to reset each month to 1.  Otherwise we end up with the first week of January on row 1 of the calendar and the last week of December on row 52.

The solution is simple:  Take the week number and subtract from it the week number of the first week of the month.  This ensures no more than 6 rows ever appear within your calendar table.

The [Select All] field, at first glance, has no practical value.  It's nothing more than a fixed text field.  What it allows on the dashboard, however, is for the user to have a quick way to select all dates available within the calendar.  Since it sits at the top of the Rows shelf, clicking on that bit of text highlights every cell within the table, which in turn fires an action to the other graphs within the dashboard.  Perfect.

So what did this exercise accomplish?  Apart from cementing my familiarity with makeshift calendar controls, I quickly noticed a meteoric rise in my average weekly spending at restaurants.  I went from $12 a week in Q4 2010 to over $60 a week in Q3 2011.  Eek.  Guess it's time to start packing lunch again...