Our Certified Tableau Trainers are asked a myriad of questions each day. In this series, they share some answers.

When asking a recent Tableau class about how they might use Tableau’s mapping features, I had one course member state that a view he currently uses (and is looking to transfer into Tableau) shows the % change in sales from the previous period by either a regional, state or city level of detail. He wanted to show this % change on Color and the current period’s sales as the size of the circle on the map.

For this example, I used the Orders and People sheets from the Global Superstore dataset. I added three years to Order Date so I could work with current dates; this is my [Updated Order Date] field.

Using and editing a table calculation won’t work because the “temporary table” for a map will not include any date information; in our case, it will compute the calculation based off of the alphabetical order of the states. Like this:

We need to use table calculation’s cousin, the calculated field.

Creating the Parameters

I have compiled a list of date measures, like Year-to-Date, below, so I am going to skip past that part for now and jump into what I did after defining those different date measures.

I wanted to make sure he could evaluate off different measures, so I knew parameters would come into play to give the user that flexibility. I created two integer-type parameters, with lists associating integers with different measures. The first parameter did this for current measures (YTD and MTD) while the second did it for our bases of comparison (those same measures for the previous year):

Creating the Calculated Fields

I then had to create two calculated fields referencing those two parameters. This is what actually ties the measures to the parameter; the “Display As” in the parameter dialogue box is an alias and is not actually referencing those measure fields:

With the two calculated fields created, I then created my % Change calculated field:

Notice that I use the SUM() aggregation here. These data points occur in different time periods, but they have been singled out by my other calculated fields, so I can simply compare their sums down at the bottom:

Creating the Map

Now for creating the actual map. My student wanted to be able to look at the data at different levels of a geographic hierarchy, including some that were specific to his organization, and by the manager in charge of a certain region.

In this data, Managers from the People sheet line up with the Regions, so I created a geographic grouping of states following the given regions, then I placed this in my Country-State-City hierarchy. I then created a parameter very similar to the other integer parameters I had previously created, with different integers referencing different geographic levels. Of course, I tied this to actual dimensions through a calculated field. This was all to allow the user to easily switch between geographic levels without having to drag pills on or off of Detail in the Marks card:

I then brought my calculated field representing current sales measures to size and my % Change field to Color. The final step was to make sure the user could interact with all of these parameters, by right-clicking and selecting Show Parameter Control on all three of my parameters:

Some of the sales numbers for Global Superstore can get wonky and see quadruple-digit percent increases, so I capped the color scale at 500%. That way, the viewer can see that dark blue and hover over it to investigate further.

Calculations

Year-to-Date Sales

IF [Updated Order Date]<= TODAY()

AND DATEDIFF('year', [Updated Order Date], TODAY())=0

THEN [Sales]

END

 

Month-to-Date Sales

IF [Updated Order Date]<= TODAY()

AND DATEDIFF('month', [Updated Order Date], TODAY())=0

THEN [Sales]

END

 

Previous Year Sales

IF [Updated Order Date]<=TODAY()

AND DATEDIFF('year', [Updated Order Date], TODAY())=1

THEN [Sales]

END

 

Previous Year’s Month-to-Date Sales

IF DAY([Updated Order Date])<=DAY(TODAY())

AND DATEDIFF('month', [Updated Order Date], TODAY())=12

THEN [Sales]

END

 

Same Month, Previous Year Sales

IF [Updated Order Date]<= TODAY()

AND DATEDIFF('month', [Updated Order Date], TODAY())=12

THEN [Sales]

END

 

Previous Year’s Year-to-Date Sales

IF DATEPART('dayofyear', [Updated Order Date]) <= DATEPART('dayofyear', TODAY())

AND YEAR([Updated Order Date]) = YEAR(TODAY())-1

THEN [Sales]

END