This is a technical follow up on the previous article on designing and working with cubes in Tableau.
The data set and structure is still the same as described in the previous article, however we are adding one calculated measure in the cube to illustrate some points regarding the Date dimension.
CREATE MEMBER CURRENTCUBE.[Measures].[Sales YTD]
AS Aggregate(YTD([Order Date].[YQMD].CurrentMember),
In order to use the relative date filter in Tableau it is important that the Date dimension is setup right. In this article walkthrough a Date dimension that is set up correctly for using relative date functionality of Tableau.
For setting the dimension up we need the following:
First create a dimension as usually with a hierarchy like Year – Quarter – Month – Day. This should be standard stuff.
Settings for the dimension
My minimum settings for the Year attribute are
Some functions in the cube can use the type settings and the same for some frontend tools (but not Tableau)
Please notice the settings for Key Columns, Name Column and Value Column.
The same has been done for Quarter, Month and Day (for Day the key column is Date)
If we turn our attention to Tableau again and look at how we can use the date dimension:
First try to do create this table
Use the new Sales YTD measure and add Year and Month from the YQMD-hierarchy
It looks nice and our Sales YTD is working.
However if you move Month to the Columns-shelf it will no longer look nice and condensed.
Select Uniqueness to be by Name and not by Key for the Month attribute
This doesn’t depend on any specific setup of the data dimension and it can be used on all dimension attributes
When you are using a relational data source and add a date to the filter shelf it looks like this
We get a special dialog box where we can choose how we want to filter the dates. Please notice that the Order Date-attribute is green.
On a cube the date will look just like any other dimension – Tableau has no knowledge of this dimension being special.
Notice that the Order Date.Month filter is blue. That means it is a discrete field and that there exists only this values. If the pill is green it means that field is continuous.
Because of the added value column in the Date cube dimension we can change the data type to date and the field will work as a continuous field.
And press OK and you get the last 6 month relative to today – meaning that the viz will be updated every time the month changes – automatically.
Also notice that March 2013 includes the sales from January and February 2013.
This way you can make the visualizations much more dynamic and there is no need for the end user to change the filters just because of month change or day change.
One word of caution.
Be aware that the cube will return data at the level of the selected dimension attribute.
Saying that Date.Month will return 12 results per Year and Date.Day will return 365 days a Year. And Tableau can’t aggregate the numbers from the cube. If the visualization is not at the level same level as the filter Date-attribute then we will then get the dreaded stacked marks.