Enhance your analysis by combining sets with parameters

Introduction

I previously talked about the difference between sets and filters and how to use sets effectively here.

As part of my week 2 training at DS we were given the opportunity to pick our own workout Wednesday challenge and present on it. The Workout Wednesday is a great weekly exercise to keep your analytical brain sharp and learn new tips and tricks by getting your hands dirty with different challenges across different tools! My pick this time was #WOW2024 | 06 | Top & Bottom Variance. This challenge required the use of both sets and parameters. As luck would have it, I had just learnt about sets and parameters this week and was keen to put my learnings to the test. So, follow along with me to learn how to enhance our analysis by combining sets and parameters.

Challenge

This challenge uses the latest version of tableau superstore data you have available. The aim is to create a viz that shows the sales variance of the top and bottom performing states in comparison to the previous month. This can be done in a few different ways as the challenge will suggest but I have chosen to utilize sets and parameters to arrive at the solution.  This can be broken down into three parts.

Part 1: Set up the parameter

First, we want to set up a parameter that will allow the user to select the month they are interested in. So, for e.g. selecting July 2021 will show the variance in sales between July 2021 and June 2021.

1. Create a calculated field for Month of Order date

Formula: DATE(DATETRUNC(‘month’, [Order Date]))

2. Create a Parameter and name it ‘Select A Month’ and set ‘Date’ as the data type. Change the display format to Custom and enter the format as “MMMM YYYY”. Then select List for allowable values and then select ‘Month of Order Date’ from the Add values from dropdown box.

3. Create another Parameter and name it ‘Top N State’ and set String as the data type. This allows the user to define how many of the states they want shown.
   

Part 2: Creating Calculative field to find the Sales Variance:

1. Create a calculated field for selected Month Sales

Formula: IF [Month of Order Date] = [Select A Month] THEN [Sales] ELSE 0 END

2. Create a calculated field for the previous month sales.

Formula: IF [Month of Order Date] = DATEADD(‘month’, -1, [Select A Month]) THEN [Sales] ELSE 0 END

3. As we have created the calculations for selected month sales and previous month sales, we can figure out the sales variance between these two by subtracting. So, let’s create another calculated field named Sales Variance

Formula: SUM(ZN([Selected Month Sales])) – SUM(ZN([Previous Month Sales]))

 

Part 3: Create a Combined set

Why create a combined set? Well, we only want to see the variance of the states that exist in the TOP N or BOTTOM N set of the data, rather than all the states.

1. Right click the state dimension to create a Top N State Set as per below.
   

2. Right click the state dimension and create a Bottom N State Set
   

3. Now we have to form the combined sets by holding Ctrl + clicking on top and bottom N sets.
   
   4. A box appears and options to select particular joins. Configure the joins as per below.
   

Final result:

This gives the user the flexibility to select any month via the parameter and obtain a viz that shows the sales variance across the states. They can further refine the view by selecting the number of states to be shown across the TOP and Bottom of the spectrum. At a glance we can see which states have improved their sales month over month and which states have drastically declined. Now I challenge you to try this same exercise for Y-o-Y comparison!