Another question that came up in my Learn what the Data School Learns was how to use parameters in YTD calculations and line charts. I did not have a working example prepared at the time and so instead demonstrated a previous Makeover Monday using this technique. This blog is a follow-up on that demo with step by step instructions on how to create a dashboard similar to the one I showed during my session.
Create your line chart
Right click and drag your date field onto the columns shelf and select continuous year and drag your measure value onto rows. This will create your standard line graph that we will be building onto.
Find your 'current year'
This will be the maximum of your year to date field. This can be done with a DATETRUNC calculation as below:
I have chosen MAX as I want to compare my chosen point to the current year however, using MIN would return the first date in the data set for a different purpose of analysis. If you want to compare to a specific date then creating a calculated field containing only that date and using that as the date field in the above calculation would also work.
Why DATETRUNC?
DATETRUNC will truncate your chosen date field to the specified level and will return a date field. This can be demonstrated in the table below:
Here we can see that Tableau has taken our date field and converted it to a date at the yearly level for each field. By using DATETRUNC for the maximum value we have also ensured that each record has a corresponding maximum date, this will be important in later calculations.
To understand the different date calculations in more detail check out this blog: https://www.thedataschool.co.uk/gordon-hack/datetrunc-datepart-datename
Calculate your current year value
This can be done using the below calculation:
Essentially we have told Tableau to return the sales value for all fields where the truncated year value matches that of our maximum field year. So in this case, Tableau will return all sales values from 2021.
Set up your parameter
This is the interactive part where our user can choose the point from which they want to compare sales data. To do this we need to set up a parameter using our date field:
As we created our parameter from a field, Tableau will automatically populate the values. However, this will be to the exact date. As we are working on a yearly level we need to change the display format to a custom format as above. Once this is done click OK.
Set up your selected year calculation
This is done in pretty much the same way as the current year calculation but instead of matching to our maximum year value we are matching to our parameter:
Bringing your values onto the view
As we now have 3 values that need to be on one axis we need to utilise 'Measure Values'. Tableau can only create a dual axis chart and so this is a simple way to get around that limitation. I did this by dragging the 'Measure Values' pill onto my rows shelf and double clicking to edit:
Here I have selected only the current year and selected year calculations.
Create a dual axis chart
Right click on your 'Measure Values' pill and select 'Dual Axis'. Do not forget to right click on an axis and synchronise to ensure your marks are aligned.
Adjust your chart accordingly. I use the marks card to set my initial Sales line chart as a 'line' rather than 'automatic' and the 'Measure Values' marks as a circle and resize.
Set up a dynamic title
If you want your chart to show a percentage change from your chosen date to the maximum date the you will need another calculation:
Format this as a percentage by right clicking on the calculation pill and selecting 'Default Properties' and 'Number Format':
By selecting a custom format you can use arrows to identify if the percentage is increasing or decreasing. These can be copied and pasted from this site: https://unicode-table.com/en/sets/arrow-symbols/#up-arrows
Now all that's needed is to bring all these elements together in your title, to do this all your calculations need to be on the Details marks card.
Bring all these elements together in your title and format accordingly.
As we are working with a currency, it is also a good idea to go back to your selected and current year calculations, open the 'Default Properties' - 'Number Format' window and set up a custom currency format as below:
Incorporate colour
You can also use a calculation to highlight when your selected year falls above or below your current year. Drag the below calculation onto your 'Colour' marks card and select an appropriate colour.
All that's left to do now is format! I like to choose a neutral grey for the line, hide one of my axes and format my gridlines as appropriate.
KPIs and Sparklines
This is a great use case for these types of charts. In the below dashboard I have duplicated my formatted chart and filtered each one to a different Region.
This creates four separate 'cards' that update dynamically with the same parameter and provide an excellent point of comparison at the surface level.
By properly formatting your dashboard you can create a view that resembles the below:
The calculations required to build this view can seem intimidating but, as with everything, they get easier with practice! There are some great videos on The Information Lab YouTube that walk you through the process step-by-step which I recommend if you need further clarification.
