As the original Part 3 of my Calendar View series turned out pretty long, I decided to split it. This is then the Part 4, showing you how to build a 12-month calendar. With labels!
I continued with my work from Part 3, so check it out if you haven’t read it yet.
If you’re stuck, check out my public workbook with all the views described and don’t hesitate to contact me.
12 month calendar
I know that some people might be interested in showing a whole year in one view. So let’s get down to business!
To continue, grab a full calendar we created in the previous section.
How to
We’ll need some calculations to specify which row and column each month should be. Let’s start with rows, create a calculated field called ‘Year Calendar Rows’. We’ll be creating a vertical option (4 rows by 3 columns) so we want each quarter to be in one row. There are a couple ways to do it:
//Option 1
DATENAME('quarter',[Full 2018])This works well and it’s an easy calculation, we don’t necessarily need a separate field for it. The thing to consider is what are your Fiscal Year settings. If the start of FY is set to a month different than January but you want your calendar to start in January, you might want to use the second option:
//Option 2
STR(CEILING(DATEPART('month',[Full 2018])/3))What’s happening here? Whenever the month number division by 3 returns a decimal number it will be changed to the first larger whole number. And so Jan, Feb and March (1, 2, 3) would return 0.3, 0.6 and 1 in division but with ceiling function they will be all changed to 1. I added a string function to force the field to be categorised as a dimension.
Pick whichever method you prefer and let’s create another calculated field called ‘Year Calendar Columns’. This one is quite easy, we just need the remainder of month number division by 3. Tableau doesn’t seem to have a modulo function which is finding that remainder, but thankfully, it has a modulo operator (%). Modulo will return 0 for March, June, September and December so we need another if-statement to change that 0 to 3 (IIF function is a one-line if-statement):
STR(IIF(DATEPART('month', [Full 2018]) % 3 = 0,3, DATEPART('month', [Full 2018]) % 3))Again, I used the string function shortcut to force the field to be treated as dimension.
OK! Let’s drag the created fields on rows and columns shelves, but make sure they are first in the queue. This is what you should have:
It may seem broken, but we’re actually very close.
Let’s drag MONTH(Full 2018) from columns shelf and put it on details.
Looks better, doesn’t it? Although, it does seem quite busy and it’s difficult to find a specific month quickly. To solve this, let’s add the row and column dividers, set them to the widest option and to white colour. Also while you’re at it, get rid of the shading.
Slightly better, but the month is not as obvious as it could be with month labelled. The next techniques are a combination of Lorna‘s post, Andy’s post and my own ideas.
Option 1
In my opinion, that’s the easiest way to get month labels. The downside is that you’re limited with where you can have your month labels (either to the left or right).
Change Month Week on rows shelf to continuous:
Change the mark type to Gantt Bar, right-click on Number of Records and drag it onto Size on marks card, pick AVG as the aggregation method. This trick will change the height of the Gantt bars, if you click on the Size on marks card, you can only change the width. Click on Label and change Vertical Alignment to middle:
- double-click on the rows shelf
- type AVG(0)
- create a dual axis, synchronise axes
- edit either of the axis and check ‘Reversed’
- change mark type on AGG(AVG(0)) card to line
- drag measure names off Color on All card
- drag measure names off Color on AGG(AVG(0)) card:
In case you have some problems with the gif above, this is what you should have now:
You can see that Number of Records trick for Gantt bars is still on our ‘line’ chart. Let’s drag it off, together with DAY(Full 2018) associated with text.
Change MONTH to Label:
Open the Label menu, check ‘Show mark labels’, select line ends and depending whether you prefer your label to be on the left or the right, keep the ‘start of the line’ or ‘end of the line’ respectively and uncheck the other option. I prefer it on the left, so I’ll keep ‘start of the line’ checked and ‘end of the line’ unchecked.
To have the label slightly further from the edge, change the alignment of the label to right (or left if you kept ‘end of line checked’):
Looking good! Let’s hide the line by decreasing opacity to 0%. There are still some lines on the calendar that we might want to get rid of. Right-click anywhere on the white space within the chart and select ‘Format’. In lines formatting, change the Grid Lines to a solid line and back to None (it originally said ‘None’ but it wasn’t true for all settings… ridiculous), and Zero Lines to None. Hide headers for all pills and there you go!
Well done, now you have a calendar with labelled months!
You can control the spaces between the rows/quarters by editing the range of either Month Week or AVG(0) axes.
Option 2
This one allows you to have labels in the middle but definitely requires more fiddling around. It gives you, however, the opportunity to control the spaces between columns of months which you can’t control that much in Option 1.
If you’re one of the crazies who start their week with Sunday, you can skip the calculated field and use WEEKDAY(Full 2018) you already have on columns shelf, just change it to continuous like we did with Month Week.
For those who start with Monday, create a calculated field ‘Weekday Starting Monday’:
{FIXED [Full 2018]:
MIN(IF DATEPART('weekday', [Full 2018]) = 1 THEN 8
ELSE DATEPART('weekday', [Full 2018])
End)}What’s happening here: we want to specify the numerical value of a weekday for every day in Full 2018, hence the LOD. Let’s me explain the second part… As it turns out, no matter how you set up your data set Date Properties (I mean the week start), the weekday number will always start with 1 for Sunday. This is crazy considering the week calculation seems to be working in line with you custom setting… (WHY?!)
To solve this, we need to overwrite the weekday number. This is what happening in the second part of the LOD calc (after the colon). In this case, if the weekday number is 1 (aka it’s a Sunday) we want to change it to 8, for other weekdays we keep the weekday number. You can also change it to 7 and decrement all other values, but that is just more typing… As long as the weekdays are in the right order, you should be fine.
Replace the WEEKDAY(Full 2018) with Weekday Starting Monday: right-click on the latter and drag it on to columns shelf and select MEDIAN from pop-up menu. Now drag off the WEEKDAY(Full 2018).
Sunday-lovers! You can join in now.
With a continuous weekday information, you can see something similar to this:
Change the alignment of labels to middle and narrow down the width of the sheet. As a final correction, hide the header for the continuous weekday.
THERE. YOU. GO. A 12 month calendar with months labelled in the middle:
boom! Byeeeeee
