This was a really interesting one and my second Wednesday workout so far. I will be honest; I am getting kind of hooked to it. The challenge can be found here . Thanks to Rody Zakovich for such an interesting challenge @RodyZakovich. It took me a while to crack this tough nut but I had so much fun doing it. This is a wonderful way to get better at Tableau, because it forces one to think in ways one would not otherwise.
Click to go to the Interactive Viz
Step 1:
To start, the data (Orders) needs to be union-ed with itself twice, giving us a new dimension called ‘Table Name’ with values “Orders”, “Orders1”, and “Orders2”. This is very important, because the aim of this exercise is to drill down into detailed data within the same worksheet. So “Orders” will be used to hold the Data year-wise, “Orders1” will be used to hold the Data month-year-wise, and finally “Orders2” will be used to hold the Data at the date level, which is the most granular level of data.
Step 2:
Create X Axis dimension. Here the X axis is the Order Date axis and is the most important one as the drilldown happens on the date axis as follows: Year->Month->dates. So we segregate the dates into year, month and dates somewhat like this, based on the Table Name:
CASE [Table Name]
WHEN “Orders” THEN
str(year([Order Date]))
WHEN “Orders1” THEN
str(DATENAME(‘month’,[Order Date]))+”-“+str(year([Order Date]))
WHEN “Orders2” THEN
RIGHT(str([Order Date]),2)
END
Also, if you notice, I have converted this into a string because keeping it as a date dimension, which converts it into hierarchy of year to month to date, which sounds like what we are after but makes our life a bit more complex. So I chose the easy way out, converted it into a string. In doing so it brings another trouble along with it, i.e the months and the dates are not sorted anymore. So to combat that we create a new variable called ‘Sorted’, which looks like this:
CASE [Table Name]
WHEN “Orders” THEN
year([Order Date])
WHEN “Orders1” THEN
MONTH([Order Date])
WHEN “Orders2” THEN
DAY([Order Date])
END
Now we will go to the next step. We will use this measure later while building the viz.
Step 3:
Build two measures: Sales for Bar and Sales for Line. The detailed view for each date is a line chart. The measures are as follows:
Sales for Bar –
CASE [Table Name]
when “Orders” then
[Sales]
WHEN “Orders1” then
[Sales]
when “Orders2” then
NULL
END
The first one level of drilldown will use a bar graph, hence Orders2 is NULL; similarly for Line graph, only the last one Orders2 is required, hence Orders and Orders1 is NULL
Sales for Line:
CASE [Table Name]
when “Orders” then
NULL
WHEN “Orders1” then
NULL
when “Orders2” then
[Sales]
END
Step 4:
We are ready to build the viz now, so lets drag the X Axis to Columns, Sales for Bar to Y axis and Sales for Line to Y axis again. Make sure on marks, convert the ‘Sales for Bar’ to Bar graph and ‘Sales for Line’ to line graph. Now right click on the ‘Sales for Line’ to convert it to Dual Axis and Synchronize axis. Uncheck the ‘Show Axis’ for the right hand side Y axis. We are halfway through now.
Step 5:
This is where the magic happens. Create four dimensions, ‘Date Year’, ‘Month Year’, ‘1st Level’ and ‘2nd Level’ . These dimensions will be used to set filter in the viz as we drill down and back. The first two dimensions are just Year of Order date and Month and year of Order date both of the type string. This is how the other two dimensions look:
2nd Level:
case [Table Name]
WHEN “Orders” THEN
[Date Year]
WHEN “Orders1” THEN
[Month Year]
WHEN “Orders2” THEN
“”
END
Drag ‘2nd Level’ to Details of the Mark for both bar and line graphs. This becomes our source later during dashboard filter creation. I will get back to this later.
1st Level:
case [Table Name]
WHEN “Orders” THEN
“”
WHEN “Orders1” THEN
[Date Year]
WHEN “Orders2” THEN
[Month Year]
END
Drag our worksheet to Dashboard and go to Menu – Dashboard – Action – Filter. Here choose source sheet as our data source and not the Dashboard because we are trying to filter based on the Unions we did right at the beginning (Orders, Orders1 & Orders2), target sheet is the worksheet itself. We choose ‘Leave the filter’ (Leaves the filter in target sheet) for clearing the selection. Lastly in target filters we chose the source field as 2nd Level and Target Field as 1st Level. Please see below:
Click on your viz and you have set an endless loop. It starts working.
How does this work ?
When you put the 2nd Level dimension on details of Mark, it picks up the X Axis time detailed level at that point. In selected Fields we have made 2nd level the source and 1st level the target. Just to give you a visual reference, this is what the data looks like:
So when we click on Year, lets say 2015, it carries forward 2015 to 1st Level and X axis filters to that year, showing all the month and sales of that year from Orders1. Similarly when we click on the month on our current viz, it carries the month – year to 1st Level and filters the X Axis to date level from table name Orders2. Now if you look at the formula of 2nd Level:
WHEN “Orders2” THEN
“”
When we click on Orders2 that is the date level viz, it passes blank to 1st Level, which clears all the filters in table name Orders, hence bringing back our first viz with all the years and thus becomes an endless loop.
Lastly right click on dimension X Axis on columns, choose sort and use the measure ‘Sorted’ which we created in Step 2 as average and keep it Ascending. This will force the months and dates to be in order.
DataVisalytics 