This dashboard utilizes fake data, and was simply an exercise in multiple techniques I wanted to try. It gives an overview of Tick Bite Occurrences across the US from 2002 to 2011 per 100k Persons. It’s meant to highlight the Periodicity of the bites rather than the total number of bites by year. Periodicity is defined.
Full dashboard on Tableau Public (downloadable):
https://public.tableau.com/profile/kaledev#!/vizhome/TickBiteIncidentsintheUS/TickBiteMultiples
Periodicity is defined as a pattern of Rises and Falls across multiple years. This can be quite subjective depending on the person viewing. Therefore, I focused on the first pattern I came across and defined those years as Heavy, Medium, Slight, or None. If two patterns occur in one state, I took the most likely and threw the other out.
Generally you wouldn’t want to determine periodicity with the actual metric value, as extremes in values make it difficult to determine a pattern. Since we only really care if something has gone up or down (and not by how much), we can alter the data to index each starting year at zero, and add or decrease one for each subsequent year (example data below). This evens out the charts and makes them very compact and easy to compare and view the patterns.
I’m sure there are more elegant ways to do this in SQL Server (maybe utilizing LAG/LEAD), but I utilized a quick while loop.
/**
* PERIOD RUN
*/
Select row_number() over(order by [State],[Year]) as ID ,*,Null as PeriodRun
Into #Temp
From TickBitesPeriodsPivot
Declare @Id int
Declare @PrevState nvarchar(50) = null
Declare @CurrState nvarchar(50) = null
Declare @Prev int = null
Declare @Curr int = null
Declare @PeriodRun int = 0
While (Select Count(*) From #Temp where ID <> 0) > 0
Begin
Select Top 1 @Id = Id, @CurrState = [State], @Curr = ChangeFlag From #Temp where id <> 0
if @Prev is null --First run
set @PeriodRun = 1
else if @CurrState <> @PrevState --State change, reset
set @PeriodRun = 1
else if @Curr = 0 --No change, reset
set @PeriodRun = 1
else if @Curr <> @Prev and @Prev = 0 --Last run was a 0 and it's changed
set @PeriodRun += 1
else if @Curr + @Prev = 0
set @PeriodRun = 1 --Fall then a rise or reverse, reset
else if @Curr = @Prev
set @PeriodRun += 1
update #Temp set PeriodRun = @PeriodRun where ID = @Id
update #Temp set ID = 0 Where Id = @Id
set @PrevState = @CurrState
set @Prev = @Curr
End
I had always wanted to try a Hex Map and it ended up being incredibly easy to setup and display – I love how compact it is and how is displays all states as the same size. Essentially – it’s nothing but an (x,y) mapping of shapes on the sheet, in which we’ve already assigned a state name. In reality it’s a fake map – but looks very complex.
I won’t attempt to redo the tutorial right now since there’s another one from several years ago that is great located here:
https://www.tableau.com/about/blog/2017/1/viz-whiz-hex-tile-maps-64713
Lastly – to get around the problem of having 50 states needing to be in a small area, I created small multiples. This isn’t great in Tableau as you have to “hack” it a bit with two calculations that mathematically divide up your dimensions (one called ColumnDiv and one called RowDiv)
--RowDiv INT((INDEX()-1)/(ROUND(SQRT(SIZE())))) --ColumnDiv (INDEX()-1)%(ROUND(SQRT(SIZE())))
It’s important as you add these to the Column and Row respectively to calculate using the dimension that is within your detail. In my case this would be “State”. Note that my Year is within Columns and my ChangeIndex (metric) is wihin the rows. Don’t get confused by the dual axis with Zero – I’ll explain that below…
The big challenge within small multiples is how to label them. There are a few methods but I chose the “least worst” option. I created a metric named Zero with the value of 0 and threw that as a dual axis on the row. This created a line with the value of zero in the same chart, which I then reduced the opacity to 0%. You can see how it is still there invisibly below. After that I set the label to the bottom right corner.
Finally – you’ll see that I’ve color coded portions of every multiple based on periodicity. Here we see Maine is “Heavy” in periodicity as 9 out of 10 years show a discernible pattern. California on the other hand is “Slight” as only 3 out of the 10 years show a pattern. Clicking on the color legend in the dashboard will highlight each pattern individually.
