As data becomes more and more integral to business decision-making, the ability to effectively visualize that data is increasingly important. In my recent project, I was tasked with visualizing ticket sales from this month compared to the previous month. To accomplish this, I opted for a line chart, as it is a clear and concise method for displaying monthly trends. While I cannot use the actual data from the project due to confidentiality, I will be using Tableau’s sample superstore data to demonstrate how to create an insightful and visually appealing line chart. In this tutorial, I will take you step-by-step through the process of creating a line chart in Tableau to compare sales from this year to previous years, so that you too can turn your data into actionable insights.

Step1: Create the “Current Year Parameter”

Step1: Create the "Current Year Parameter"

After Drag “Order Date” and SUM(Sales) into the view, we create a Calculated Field called “Current Date Parameter”, which allows you to choose the current date(year).
Step2: Create the Current Year and Current Year Sales Calculated Fields

The boolean expression “Current Year: DATETRUNC(‘year’,[Current Year Parameter])=DATETRUNC(‘year’,[Order Date])” compares two dates and evaluates to either true or false.

The expression compares the year of the ‘Current Year Parameter’ to the year of the ‘Order Date’ by first using the DATETRUNC function to extract the year from each date. The DATETRUNC function is used to remove any smaller units of time, such as months, days, or hours, and only keep the year part of the date. This is done to ensure that only the year is being compared, regardless of the month or day of the order date.

If the year of the ‘Current Year Parameter’ is the same as the year of the ‘Order Date’, the expression will evaluate to true, indicating that the order date corresponds to the current year. If the years are different, the expression will evaluate to false, indicating that the order date is from a different year than the current year.

The statement “IF [Current Year] THEN [Sales] ELSE NULL END” is a conditional statement that evaluates whether the current year is true (meaning it exists) or not. If the current year is true, then it will return the value of sales. However, if the current year is false (meaning it does not exist or has not been defined), then the statement will return a null value.

Step 3: Create Prior N Year Parameter, Prior N Year Calc Field and Prior N Year Calculated Field

 

To start, we will create a parameter named “Prior N Year” with an Integer data type. Given that we only possess data for three consecutive years, we will restrict the allowable values to a range between 1 and 3.

The statement “DATEDIFF(‘year’, DATETRUNC(‘year’,[Current Year Parameter]),DATETRUNC(‘year’,[Order Date]))= – [Parameters].[Prior N Year]” is a comparison statement that checks whether the time difference between two dates (the “Current Year Parameter” and the “Order Date”) is equal to a negative value of the “Prior N Year” parameter.

Likewise, We calculate the Prior N Year Sales.

Step 4: Dual Axis

To create a comparison line chart comparing Prior Year Sales to Current Year Sales, we can move the two measures to the Rows shelf and create a dual axis chart. After synchronizing the axes to ensure consistency on the y-axis, we can format the chart title and add a comparison line to show the difference between the two measures.

 

In summary, I have produced a line chart containing two measures that display the variance between two years. In my upcoming tutorial, I will demonstrate how to generate a dynamic ups and downs  KPI arrows with different colors to indicate trends in the data. I hope you found this tutorial useful, and please do not hesitate to share your feedback or inquiries. Thank you for following along!

 

 

The Data School
Author: The Data School