This post is a continuation of part 1 on how to create the Omniture visits report in Tableau. Click here to go back to part 1
This is the chart we are trying to create, the same as the Omniture calendar month visits report.
Now we can begin to create the chart. Begin by putting the Day pill to the Columns shelf and the calculated field SelectedMonthVisits on to the Rows shelf. Make sure the Day pill is displaying the DAY(Day). The chart should look like this:
Calculated Field for 4 week prior visits AND the selected month last year visits
The next step is to be able to show the visit data for the visits 4 weeks prior and the data for last year. Both of these visit counts need to be in the same calculated field as they’re are both going to be displayed as a line in the final chart. This 4 weeks prior data was loaded in the Custom SQL self join – it’s the field WeeksPriorVisitCount.
The field for last years visit data is in the VisitCount field.
To begin in our calculated field, which will be called 4WeekPriorVisits, first sum the 4 weeks prior data – the formula being:
SUM(IIF(month([Day]) = month([DateSelector]) and year([Day]) = year([DateSelector]) ,[WeeksPriorVisitCount] ,0 ))
Last years visit data also needs to be included in the calculated field. To do this we need to add another IIF statement in the Else clause selecting the same month last year. The final formula is:
SUM(IIF(month([Day]) = month([DateSelector]) and year([Day]) = year([DateSelector]) ,[WeeksPriorVisitCount] ,IIF(month([Day]) = month([DateSelector]) and year([Day]) = year(dateadd('year',-1,[DateSelector])) ,[VisitCount] ,0 )))
Build the Chart
Next add the newly created 4WeekPriorVisits pill to the Rows shelf alongside the SelectMonthVists pill. This will give 2 line charts on top of each other. We now have all of the data we need on the chart, we need to format this to display the data as we would like to see it.
Make the chart into a Dual Axis chart. Click the arrow to the right of the 4WeekPriorVisits on the Rows shelf (this should be the right hand pill) and select Dual Axis. The chart should now look like the following:
Display the bars for the SelectedMonthVists. To the left of the chart there’s a section called Marks. This defaults to Automatic. Click the arrow to the right of Marks and select Multiple Mark Types.
Scroll through the Mark Type options to find the AGG(SelectedMonthVisits). Change this from Automatic to Bar.
We need to split the 4WeeksPriorVists and last year visits line charts. Select the AGG(4WeeksPriorVisits) mark type. Drag the Day pill into the Color shelf and make sure it’s set to YEAR(Day). You might need to edit the colours if the defaults cause some clashing.
There are still some formatting tasks we need to do with the Secondary Axis. Right click on the secondary axis and tick Synchronise Axis. Also Format the axis and colour the ticks, font, etc white to hide the secondary axis.
Fix the X axis
Regardless of the month selection, the X axis always shows 31 days. To alter this to show the correct amount of days use a calculated field and some filtering.
Create a calculated field, call it DateFilter and enter this formula:
IIF(MONTH([Day] ) = MONTH([DateSelector] ),true,false ) //This is needed to make sure the correct number of days are showing in the X axis //without this the number of days on the X axis is always 31
Drag this DateFilter pill into the Filter shelf and set the value to True. This will mean Apr shows 30 days on the X axis, May 31, etc.
This chart is now more or less complete. The tooltips and labelling needs to be tidied up but I’ll leave that part up to you :-)