Reporting on the most visited pages within your website can be a rather static endeavor if your most popular content doesn’t change very often. Changing this report to a list of pages with the largest change (both positive and negative) in visits is most likely a lot more interesting.
I often look at a top sorted list for both percent change and absolute change. Both are available as quick table calculations but it requires a bit more work to get them to sort automatically.
For this example I’ve used the Sample – Superstore – English (Extract) dataset which comes standard with Tableau Desktop. Because this dataset doesn’t contain webanalytics data I’ve used similar data such as Order data, Category and Sales.
So let’s setup the worksheet.
Drag “Order Date” on the Columns shelf and select the Discrete Month/Year option. Drag “Order Date” on the Filters shelf, also select Month/Year and only select the months you would like to compare. I’ve used November and December of 2013. Drag “Category” to the Rows shelf and drag “Sales” to Text in the Marks card. This should give you the sales of each category for both November and December 2013.
Quick Table Calculation – Percent Difference
Right click on the SUM(Sales) pill in the Marks card and select Quick Table Calculation > Percent Difference.
This should give us the percent difference for each category for December compared to November. You could also select “Difference” which will show the absolute difference instead of a percentage.
Automatic sorting on absolute values
Now let’s make sure this is sorted correctly (independent of positive/negative change).
Ctrl click the SUM(Sales) pile in the Marks card and drag it to the Rows shelf, on the right of the Category pill. Right click the SUM(Sales) in the Rows shelf, select Discrete and drag the pill to the left of the Category. This will cause Tableau to sort the table by the first field which is now our percent difference. Don’t worry if your table looks funky, we’ll fix that.
Right click SUM(Sales) in the Rows shelf and select “Edit Table Calculation” then select “Customize”.
Rename to “Sales Percent Difference for Sorting” and use the following formula:
-LOOKUP(ABS((ZN(SUM([Sales])) - LOOKUP(ZN(SUM([Sales])), -1)) / ABS(LOOKUP(ZN(SUM([Sales])), -1))),LAST())
Notice that this is a negative Lookup because we want the sort with the highest number first. By making the formula absolute (ABS) both positive and negative change are treated equal in this sort. This should give the following result.
Now let’s clean this view up. We have no need for the sorting field to be visible so we can hide it by right clicking on “Sales Percent Difference for Sort” pill in the Rows shelf and unselect “Show Header”.
We also have no need for displaying the column “November 2013” as only the column “December 2013” holds values. Right click the column header for “November 2013” and select “Hide”.
This should give you the following result. You can download this workbook from Tableau Public by clicking download on the bottom right just above the Tableau logo.
What are your thoughts?
How do you use Tableau? Do you have any other techniques for creating this view within Tableau and what are your best practices? Feel free to leave them in the comments!