Off the Charts Consulting
  • What we do

What are we Talking About

Build the best interactive calendar in Tableau

2/12/2020

0 Comments

 
I've been obsessed with building a Calendar in Tableau. I've tried more than once to sneak it into a client's dashboard, but it's reception rarely matches my enthusiasm for it. 

Today, amidst some tremendous procrastination, I've finally built myself a top tier interactive Calendar. In my opinion, anyway. 
Tableau public link at the bottom. 

Who is this view for? I think the best example, which I don't have great data for, is for future planning. I had an event planning client who could see upcoming room capacity by day. Or shift scheduling for a workforce. But the Calendar works well enough for past results too. I've done hours worked by day. You could also do Revenue, Net, Orders, Customers. Any of your KPIs, really, but you'd want something that occurs Daily. New Hires, for example, might be a bad fit. Anyway. I digress. Let's look at a picture. 
Picture
Staging the Data
This is a view of every day this year, and how many hours I've worked on that day. It's also showing future dates with no hours. That's one of the first steps to setting up this type of view. You TYPICALLY need a second table. It's just one column, and it's every day in the calendar time frame. I have an excel sheet that runs from Jan 1 2017 to Dec 31 2021. Every day needs to be represented, past and future. This is then right joined to your data on the date in your dataset. If you don't do this, your calendar will be missing days where no activity occurs in your business. Some databases might have a record regardless of activity, but that's certainly the exception not the rule. 
Picture
Building the sheets
​I'll go over the sheets themselves. I've build a single sheet and once happy with it duplicated it 3 times, and switched the months. 
​
Picture

1 - The columns. These are straight forward. The only thing worth calling out is to make sure you use the Date Field from your custom table that has every date. Otherwise you'll be missing the data on days that don't exist in your database. To get WEEKDAY you right click on the month dimension pill, go to 'More' and select Weekday. I then did a bit of formatting on my headers. 

2- Filters. The Month filter just narrows down how many Months I show on a sheet. I originally went with Quarters but if you are anti scrolling, as I am, four wide/three long does better than three by four. So I scrapped quarters and hard filtered to four month intervals. 
Only One Year - this is tied to a yearly parameter that lets the user switch. In hindsight a single value select filter on Year(EveryDay) would have been fine. 

3 - Marks. The colour is a calc field. You could easily just prop your measure on there but I wanted a bit more control so I created a calc field that lets me hardcode my colour steps. It also allows me to switch measures using a second parameter. 
Day(EveryDay) is on the text mark and is formatted to be top right. This gives the calendar look of having only the day in each box. 
The rest of this stuff is for the tool tip. You can add the measure into the text box, bottom left. It's a good look, but I found it a bit too crowded and took it out. If you don't mind the scroll then this is a good tradeoff. 

4- Rows: Week in month is actually a slightly complex calc to give you how many weeks are in each month. I stole it from the forums. 
IF DATEPART("weekday", DATETRUNC("month", [EveryDay])) = 1
THEN INT((DATEPART('day',[EveryDay])-DATEPART('weekday',[EveryDay])+7)/7)
ELSE INT((DATEPART('day',[EveryDay])-DATEPART('weekday',[EveryDay])+7)/7)+1 END

Dashboard
So after duplicating the sheet 3x and fixing the filter to bring different months on each, add all the sheets to the dashboard. The only stickhandling I did here was use a vertical layout container and select "distribute evenly" to make sure the charts all occupied the same amount of space. Then add the parameters in. One toggles measures from clients to hours, the other changes the year. 

Flair
I've also add a viz in tooltip to show year over year per day. Actually pretty happy with this one. I think it looks great and gives a bit more functionality. On my internal dashboard I've added a second tooltip that shows all the hours I did on that day, and who was billed what. 

Tablleau Public URL
Picture
0 Comments



Leave a Reply.

  • What we do