Calendar in Tableau

When you build a dashboard, you often have to let your users chose a time frame for the analysis. This blog intends to give you several possibilities to do it, from the easiest to the most complex use case.

But at the end it depends of the needs’ of your stakeholders, but also of their analytics maturity by using filters and parameters. Do not try to have a difficult solution in hands, if you can convince your stakeholders to use a less elegant, but more prosaic approach

Use the Native Time selection

The first possibility is to use the native Tableau time selection.

When you drag and drop date into the filter pane, you need to choose amongst several possibilities :

  • Relative dates : last 3 days, yesterday, last 7 weeks…
  • Range of dates : from 02.01 to 25.12
  • Starting date or ending date : from 01.01.2010..

The problem is that, whatever your choice will be, the user will not be able to change it. That is fine for let say 80% of the use case. Because the users usually want ever a specific range of dates that they are willing to enter, or a relative date.

Below what you choose, versus what the user can select.

Use a drop down menu

To facilitate the user’s experience, I have, for some projects, created some parameter of the most common time selection, so they do not have to choose between those 2 options or have to select a start date and end date for common calculation as last 7 days.

First you need to create a parameter so the user can choose the time frame he/she wants for the analysis. Here I have created 6 different options : last 7 days, last 3 months, last 6 months, last 12 months, Year to date, all time and a custom ( that will come handy later)

Now I need to create a calculation, that I will use to filter out or highlight the data depending of the user’s selection. That could look like this :

Time selection =

//last 7 days

if [Time Selection]=0 and DATEDIFF(‘day’,[Date],TODAY())<=7 then true

//last 3 months

ELSEIF [Time Selection]=1 and DATEDIFF(‘month’,[Date],TODAY())<=3 then true

//last 6 months

ELSEIF [Time Selection]=2 and DATEDIFF(‘month’,[Date],TODAY())<=6 then true

//last 12 months

ELSEIF [Time Selection]=3 and DATEDIFF(‘month’,[Date],TODAY())<=12 then true

// all time

ELSEIF [Time Selection]=4 then true

// year to date

ELSEif [Time Selection]=5 and YEAR([Date])=YEAR(today()) and [Date]<=TODAY() then true

// custom date- where the user will enter a start and end date

ELSEIF [Time Selection]=6 and DATE([Date])<=[end date] and DATE([Date])>=[start date] then true

END

Obviously, this method has some limitation, as you will have to create as many calculation as option but there is some solid use cases, where it could be handy, as my users need very specific time frame.

This calculation, can be then used in the filter pane or like in the example below to highligh the selected period

I have created 2 additional calculations to add on the title for the min and max of the selected date

Max selected date { FIXED :MAX(date(if [selected dates custom] then [Date] END))}

and then place them into the detail shelf and I place time selection into color ( purple for true and grey for false). You will also have to use the time selection calculation to filter the data in your views.

Yes, but….

I know what you are probably thinking… your stakeholders would probably tell you that having automatic selection are great, but what if they want to select a start and end date?

And that is where the last part of the time selection calculation became handy.

ELSEIF [Time Selection]=6 and DATE([Date])<=[end date] and DATE([Date])>=[start date] then true

end date and start date being parameters.

So if the first parameter equals custom (6), then the start and end date parameter are considered in the formula.

But it is not so optimal for an user, because those start and end date parameter are always in the view, making it quite confusing for the user. I received in the past some user’s questions ” Annabelle I don’t understand, I change the start and end date, but the selection is still fixed to “last month”.

Before coming up with another solution, I want to mention that you can fixed the start date or end date to a default value. When you create a parameter, you can add a value by default when the workbook opens, on the example below I create a calculation called reference month default and I selected on the drill down menu, while creating my parameter.

Where is my calendar?

Because the previous solution was not sufficient for my stakeholders, I tried to find a way to create a calendar view, so they can pick up the start and end date. The idea is that those views appears only if the parameter time selection is equal to custom (6)

Let’s decompose this technique.

First the view has been created by adding

  • Month(date) and weekday(date) in column
  • week (date) in rows ( some dates are missing by the way because the examples have been built using a transaction dataset, where there is no transaction everyday. I admit I was too lazy to create a calendar source)
  • selected dates calculation in color
  • day(date) in Label to have 1-31 written
  • Mark = Square
  • display calendar, checking if the parameter=custom (6) : that is what makes your view appears or disappear
  • then you can also see in filter a calculation making reference to month reference?=true

The last calculation displays only one month, the reference month

Month reference?

month([Reference Month])=MONTH([Date])
and
YEAR([Reference Month])=YEAR([Date])

With Reference Month being a parameter.

By default, this parameter opens on the current month because the reference month default =DATE(DATEADD(‘month’,0,TODAY()))

The “Juillet” July view is built on the same way, but pick the previous month of the so called “Reference month”.

The calendar is controlled by a parameter action on the little arrow you see on the right and left side. One is used to increment the reference month and the other one to decrement it

This view just use a shape as a mark and increment reference calculation is added into detail

Increment Reference calculation

DATEADD(‘month’,1,[Reference Month])

Then you need to add 4 parameter actions

  • One acting on the decrement worksheet
  • One acting on the increment worksheet
  • One driving the start date
  • One driving the end date

Example of the increment Action :

By clicking on the right arrow, the target parameter (reference month) will be incremented because the source field is month(increment reference) = DATEADD(‘month’,1,[Reference Month])

Example on the start date action :

by clicking on calendar month -1 the start date will be given the value of the Day(date)

That method was perfect for my use case because :

  • the calendar view appears only when the parameter time selection was set to “custom”
  • my users want an end date usually around today date
  • the time frame was less than 2 months and more than 30 days ( Marketing campaign analysis)

The last point is important because in case the time frame is higher, the display is not optimal, even if the calculation works

Here for instance, I can click on the left arrow, select the 8th of June and if the view doesn’t display the end of month, my calculation works as it selects 08.06 to 18.08.

But in case I want to have a start and end date, potentially from the same month, I will have to take a different approach.

Calendar for all cases

If I want to have a calendar that works for every case, one possible technique is having a start and end month button. The idea is : if the custom date has been selected, a pick a start and pick an end date appear.

But once I click on start date, the calendar appears.

And once the users have done the date selection, the date appears below the calendar icon.

How can we do it ? If the technique is quite similar to the one explains below. Here you will need additional views :

  • the left and right arrows
  • current month / previous month for the start date
  • current month/previous month for the end date.
  • the calendar selection.

The calendar selection consists on a inner data duplication and 2 icons. The field duplicate start/end is created by DATEPART(‘month’,[Date])%2, meaning that my data is spilt into 2 columns, which values are 0 or 1.

I can then use a calculated field “label calendar” to label “Start Date” or “End Date” depending of the value. That value will be very useful to make appear the Start calendar or End Calendar later.

Label Calendar : if [duplicate start end]=0 then “Start Date” ELSE “End Date” END

Then we need to create a parameter action. By clicking one one of the 2 icons calendar, the parameter “Start or end date?” will be given the value of the icon. Meaning that if I click on “start date”, the value 0 will be passed. I will also add a filter in each of the 4 calendars, so when the value =0 I will display the Start calendars and when the value will be one, it will be the End calendars.

Once the start calendar is open, the user has the capability to click on it. A parameter called “start date drill down” will be affected by the date choosen. You will need to create a similar parameter action for the end date. By the way, you can select a default start and end date, in my case the parameter end date is today and the start one is one month ago.

Once the user has clicked a start date, what I want is that the End Date calendar opens automatically, to do that I create a parameter action incrementing the value from 0 to 1 of the parameter “start or end date”.

Once both date are selected, I want to have below the calendar icons, the date choosen. So I create the following calculation :

if [duplicate start end]=0 and [start or end date ?]>=1 then MIN([start date drill down],[end date drill down])
ELSEif [start or end date ?]>=1 then max([end date drill down],[start date drill down]) END

I use the min and max, to override any mistake during the clicks. And then I just need to change slightly my color formula :

// custom date- where the user will enter a start and end date END

ELSEIF [Time Selection]=6 and DATE([Date])<=MAX([start date drill down],[end date drill down]) and DATE([Date])>=MIN([start date drill down],[end date drill down]) then true

I will probably update this blog, if I find a more elegant technique. But in my experience, you should always try to use the simplest possible way in Tableau, to avoid unnecessary development and increase performances.

Updated version

This week I was quite unsatisfied with the previous version, as I wanted to limite the number of vizzes in my view. One way to do it, is to add 2 months in the same view.

  • the filter now is set to 2 last selected month
  • and now you see that the 2 months are on the same view.

For that you will need some data preparation, so you have the week number that is reinitialise every month. That takes me some time to do it in Excel.

Here is the link to access the Excel : Google Drive

Take care that depending of your preference, you may have to change the start of your day ( monday or Sunday) in Excel and in Tableau.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Blog at WordPress.com.

Up ↑

Create your website with WordPress.com
Get started
%d bloggers like this: