Reference Lines And Other Helpful Month Over Month Visuals

Updated: Mar 31

Do you want a quick way to visually see month over month comparisons? In my tableau public dashboard here, I created a couple of visuals to display these comparisons. A popular combination of graphs I have seen lately is the Bar Chart with a Reference Line, paired with a Sparkline graph.


In my example I have two dashboards here you can flip through. The first dashboard is a company friendly version, the second one is an artistic version with the wood grain background. Click on the navigation icons at the top to flip through the different views.


Bar Chart with a Reference Line

Reference lines are helpful to visually see a comparison. Should you have Year over Year or Month over Month?


If its the beginning of the year, a year over year comparison may not be helpful to see in this format.


In the example below you can see that Equifax has a very small bar for the first two months of 2022. Experian has a lot of complaints for just two months and may appear to outdo their previous year number.



For the month over month comparison, this bar chart may be more helpful. In the example below the wide bar is close to the previous month reference line for all three companies. It's helpful to see that the number of complaints are lower this month compared to the previous month.



In order to create this visual I needed to calculate the current month's number of complaints and the previous month's number of complaints.


Here are the custom calculations for this visual;


Latest Date

{Fixed : Max([Month Year])}

*Month_Year is my date column


Current Month

Date(DATETRUNC('month',[Latest Date]))


Current Month Complaints

{Fixed [Company] : SUM( IF DATETRUNC('year',[Month Year])=DATETRUNC('year',[Latest Date])

AND DATEPART('month',[Month Year])=DATEPART('month',[Current Month])

THEN [Complaints]

END)}


Previous Month

Date(DATEAdd('month',-1,[Latest Date]))


Previous Month Complaints

If DATETRUNC('year',[Month Year])=DATETRUNC('year',[Latest Date])

AND DATEPART('month',[Month Year])=DATEPART('month',[Previous Month])

THEN [Complaints]

END


For the MoM △ calc in the tooltip:


Is Latest Month

DATETRUNC('month',{MAX(DATETRUNC('month',[Month Year]))})

=

DATETRUNC('month',[Month Year])


Latest Month Calc

if [Is Latest Month]

THEN [Complaints]

END


Is Prior Month Calc

if [Is Prior Month]

THEN [Complaints]

END


MoM △

(ZN(SUM([Latest Month Calc]))-ZN(SUM([Is Prior Month Calc])))

/ZN(SUM([Is Prior Month Calc]))


In order to create the visual for one of these bar charts I performed the following actions;


Filtered the dashboard by each company.

Put Current Month Complaints on the Columns shelf.

Put Previous Month Complaints on the Detail Marks card.

Put the MoM △ calculation on the Tooltip Marks card.


On the Analytics tab dragged the Reference line onto the canvas onto the Table pane.

I made sure the entire table was selected in the Reference Line pop-up menu.

I selected Sum(Previous Month Complaints) from the drop-down and made sure Total was selected.

For the Label I selected Custom and entered <value>.

I further formatted the reference line to be black.



I formatted the tooltip as below;


Now the black line will display the previous month's totals and the bar will reflect the current month's totals.


Spark Line Chart

A good visual to pair with the Reference Bar Chart is a Spark Line. You can hover over a line in the graph to see the month as it is compared to the previous month.


Create a Line chart as usual. I put my date metric (formatted it as month) on the Columns shelf and I put my Complaints count on the Rows shelf.

I filtered the chart by one of the companies.

I then placed the below calculation on the Detail Marks card of this worksheet.


Previous Month %

(ZN(SUM([Complaints])) - LOOKUP(ZN(SUM([Complaints])), -1)) / ABS(LOOKUP(ZN(SUM([Complaints])), -1))


I then placed the Latest Month Calc on the Rows shelf and made it a Dual Axis. I then Synchronized the axis.

I also formatted the text, removed the gridlines for all visuals and cleaned up the tooltip.


I also created a text sheet to further call out the current month and prior month %. Some users do not have this in their dashboards.


Here is this example in another Tableau Public visual by Daria LINK. They use the reference line for Actual versus Target metrics.

362 views0 comments