Custom date periods in Tableau
In this post we will show you how to create custom date periods in Tableau.
The flexibility of this field will be defined by number of periods displayed and periods itself (weekly and monthly in our case but new periods can be easily added). The key point here is to keep the same number of periods, e.g. user choose 4 periods and he can switch to 4 months or 4 weeks.
Custom date field with variable number of periods for different aggregation
It is relatively easy to create custom period that displays data in different time periods chosen by users.
It may be useful for some readers so we will cover how to create such field.
For this purpose, we need to create a parameter with periods. Like the one below:
Than create custom dates from the date field you want to use:
We will create three periods: daily, weekly and monthly. You can skip daily if the data is already aggregated by days. You can use only higher level of date aggregation. For example, if date field is aggregated by weeks you can use weekly, monthly, quarterly, yearly periods but not daily, hourly etc.
Now, all elements are ready and we can create calculated field:
Now we can use this field for our reporting purposes.
Custom date field with the same number of periods for different aggregation
To create date range with custom periods but same number of periods we need to implement few additional fields.
First of all, we will add new parameter “Number of periods”:
Then we need to add two calculated fields (if you recall we build second type of custom date periods for months and weeks), we will call them “Start Date of a Month” and “Start Date of a Week”:
The purpose of these fields is to find the start date of first period in specified range.
For example if we choose weekly periods and 3 as number of periods then “Start Date of a Week” will find a first day of the third week from now:
The same applies to months — it will give us the first date of the month N months ago.
Please note than for weekly data you may want to change start date of the week from US to EU standards (from Sunday to Monday as start of the week). Also you can change the start of fiscal year there adjust calculations for your company goals.
Final calculation of our metric is next:
Based on user input (period type and number of periods) we take a period that is between firs date of a period and today and aggregate.
Final step is to add this one to reports. To do this we need to add it to filter in order to display only not null values (Range filters — Special — Not null dates). Also note that you should now aggregate calculation field, it must be not aggregated to work properly. To place it as not aggregated, you can right click and drag the fieldm then choose one of the two top options ({Name of the field}(Continuous) or {Name of the field}(Discrete)):
