Tableau Set Action Explained #WorkoutWednesday W44-45

In the last 2 weeks right after or during the TC18, Tableau revealed a few very interesting features for Tableau 2018.3, one of which was an addition to our workbook / dashboard Action, which is ‘Change Set Values…’

The steps are very simple:

a. Create sets based on the drill down etc

b. Write a formula including the sets to make it behave as per your analysis

c. Create the set action and test your viz.

WorkoutWednesday Week 45

The Tableau community has been very excited with this addition, and why not. It makes our lives much more easier when building dasboards in Tableau and when detailed analysis is required. Earlier it was possible but required lot of data modelling and was slower. Here I will explain how to use Set Action for interactivity by using these beautifully done Workoutwednesday dashboards by @AnnUJackson and @RodyZakovich.

Lets get into then. First of I will display the week 45th wow dashboard here.

Here is how it is done. (Set Action Only). Make sure in the ‘Marks’ it is a square not text.

 

1. First of all, let’s create a parameter for the X axis which can be either Region or Segment; similarly create a parameter for Y axis which can be Category or Sub-Category. Now create calculated field X and Y, which is just to change the Values w.r.t the chosen option in the paramters. This is how the X looks like, see below. Y will be similar but will be along the Y axis parameter.
2.  Now create a set using the “X” and “Y” calculated fields. Remember the idea is to   highlight and not filter. So the sets will look like this, If X is ‘Region’ or Y is ‘Sub-Category’. It can be any row on which the mouse hovers over. Set Y, similarly can be any column the mouse hovers over.
3. Create a calculated field depending on the behaviour; we want a highlighted criss-cross, depending where the mouse is on the table. So the formula will be used in the color mark. So we will say if set X or Y then 1 else -1. In the color mark we will set it as max(selected) 
4. In the color pane give 1 the highlight color you like. 
5. Final piece of the puzzle: create the action sets.Test the set action and you are done.

 

 

WorkoutWednesday Week44

Now let’s have a look at other fantastic uses of Set Action.

Here are two seperate uses of set action, one being the line graph, wherein you can drill down into detailed date analysys from years to months etc.

Second is the tree map, wherein you can drill down till the product name level and its sales value.

1. The time chart is simple – Sales on Y axis and Month on X axis. We created the set time, which includes all the months in the data set, and we will be using this to filter our next worksheet, which is the TreeMap. So we put the Set in the filter area and check ‘In’ only. Set this for both line chart and the tree map worksheet.
2.  So we want to select smaller chunks of date ranges using our mouse and would want the viz to show the line chart only for that range. Simply creating the set action will allow us to achieve this, since we have the set in the filters panel. There’s no requirement of any calculated field. The “In” filter will take care of it as shown above.
3. For the TreeMap, we have created one set for a combination of Category and Sub-Category and also a calculation field to make the set action to drill down to sub-category from category
4. The formula for drilling down to Sub-Category is
5. We will use this formula to create our second set, so that we can drill down further from the resultant Sub-Cat to Product name.
6. Like always, here is the last step, creating the Set Action for drill down from Category -> SubCategory -> Productname

 

 

 

 

Workout Wednesday W29 – 2018, Order Profit

I have been participating in this for a few weeks now and am totally enjoying myself. It helps me, and anyone who participates, think in directions one wouldn’t have otherwise. Please find the challenge here.

interactive viz

I will not go into details of how I solved this weeks workout; instead I will only touch the main areas and things that makes these workouts special. I couldn’t resist myself and had to go through the spoilers.

The most important spoiler was **

• ROUND({FIXED [Order ID], [Sub-Category] : SUM([Profit])}/[Round Parameter]) * [Round Parameter]

I think  @LukeStanke was feeling very generous this time. Lets get to business now.

Step 1:

Create the Parameter of type integer and type list with all the values in it $25, $50, $100, $250, $500 and $1000. then create the calculated field using the hint as mentioned above as “Profit per order Rounded”. Make sure it’s a “Dimension” and “Continuous”

Step 2:

Filter plays a very important role. Use Ctrl and drag the “Profit per order Rounded” pill into Filter, chose range -1500 to 1500. Also filter it to category “Technology” You may or may not convert them to context filters.

Step 3:

Follow the X axis and Y axis instructions as mentioned in the workout. Create a field called “Bar Size” using the parameter you have created. Make it of the dimension type so that when you put it in the “size” mark it doesn’t do any aggregation. It is important to keep the bar width equal.

 

Step 4:

Put sub-category pill into colors. In colors use less than 60% opacity. You are almost there now.

Step 5:

Sorting is important, use manual sorting for the field subcategory, so whichever worksheet is highlighting, or whatever subcategory, keep that at the top.

Here is the final and the most fun part: the bars need to overlap, but how?

Analysis->Stack Marks->Off

Follow the formatting as required and we are done. Thanks, Luke, for another amazing workout.

 

 

Workout Wednesday Week 26, 2018

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.