Dynamic chart titles

Say what you see in Tableau and Qlik Sense

March 2020

In January 2019 the ONS decided to change the way they write chart titles. They felt that baking insight into their titling would improve memorability and ensure clearer, speedier communication with readers.

It's easy to do in a static report but more challenging in a dynamic BI environment.

I loved the impact it had on their output so I tried to mimic their approach by using calculations to create chart titles "on the fly".

It's hard to be truly insightful with a calculated title. "World tomato shortage increased Q1 sales" is tough to know just from a sales dataset. I think a more workable approach is to restrict ourselves to statements that clarify the basic trend in a visualisation.

The above year-on-year time series is a typical example. Most execs would ask how much sales changed between this January and last, right?

That's something we can calculate easily and state clearly in a chart title.

There are probably a zillion use cases for this idea but here's a starter approach for the above example with two popular BI tools I like to use. (First up Tableau, scroll down for Qlik Sense.)

The trick in both cases is calculating the first and last number in a range...

Dynamic titles in Tableau

Tableau allows you to create rich titling with colours and emphasis but it lacks a number format function. Thus you'll need to create two calculations to generate a title: One for the text statement and one for the numeric portion of your title.

To get the first and last number in a range (and calculate the difference) I used the WINDOW_SUM function.

First number in range = WINDOW_SUM(Sum([Sales]), FIRST(),FIRST())

Last number in range = WINDOW_SUM(Sum([Sales]), LAST(),LAST())

Step 1: Create a calculated measure "Sales difference" to calculate the percentage difference between our first and last numbers in the sales chart. (You could probably further split this up for readability but I already have two calcs to do so chose not to.)

IF WINDOW_SUM(Sum([Sales]), FIRST(),FIRST()) < WINDOW_SUM(Sum([Sales]), LAST(),LAST()) THEN

(WINDOW_SUM(Sum([Sales]), LAST(),LAST()) - WINDOW_SUM(Sum([Sales]), FIRST(),FIRST()))

/ WINDOW_SUM(Sum([Sales]), FIRST(),FIRST())

ELSE

(WINDOW_SUM(Sum([Sales]), FIRST(),FIRST()) - WINDOW_SUM(Sum([Sales]), LAST(),LAST()))

/ WINDOW_SUM(Sum([Sales]), FIRST(),FIRST()) * -1

END

Step 2: In the drop down pill for your new "Sales difference" measure select Default properties > Number format... > Custom and enter 0.0%;0.0%

(This will ensure we don't see a minus sign in the percentage, which isn't needed when combined with the next calculation.)

Step 3: Create a second calculation to generate accompanying text for our percentage.

IF [Sales difference] < 0 THEN

'DOWN'

ELSE

'UP'

END

Step 4: Drag your two new calculated fields to the Details area under Marks. They're now ready to use in your chart title.

Step 5: Edit the chart title and combine your calculated fields with appropriate words.

Step 6: You're done! View a working version of this on my Tableau Public page

Dynamic titles in Qlik Sense

Qlik doesn't offer the rich formatting capability of Tableau but we can use a similar approach with much fewer steps.

We can use set analysis to get the first and last number in the range.

First number = Sum({$<[Order MonthYear]={"$(=Min([Order MonthYear]))"}>} [Sale Amount])

Last number = Sum({$<[Order MonthYear]={"$(=Max([Order MonthYear]))"}>} [Sale Amount])

Step 1: Create a variable "v.SalesDifference" to calculate the percentage difference between our first and last numbers in the sales chart.

=If(Sum({$<[Order MonthYear]={"$(=Min([Order MonthYear]))"}>} [Sale Amount]) < Sum({$<[Order MonthYear]={"$(=Max([Order MonthYear]))"}>} [Sale Amount]),

( Sum({$<[Order MonthYear]={"$(=Max([Order MonthYear]))"}>} [Sale Amount]) - Sum({$<[Order MonthYear]={"$(=Min([Order MonthYear]))"}>} [Sale Amount]) )

/ Sum({$<[Order MonthYear]={"$(=Min([Order MonthYear]))"}>} [Sale Amount]),

( Sum({$<[Order MonthYear]={"$(=Min([Order MonthYear]))"}>} [Sale Amount]) - Sum({$<[Order MonthYear]={"$(=Max([Order MonthYear]))"}>} [Sale Amount]) )

/ Sum({$<[Order MonthYear]={"$(=Min([Order MonthYear]))"}>} [Sale Amount])

* -1

)

Step 2: Create a calcution function in your chart title (under Appearance > General) to generate the dynamic title...

='Sales have ' & If($(v.SalesDifference) >= 0, 'grown ', 'fallen ') & Num($(v.SalesDifference), '0%;0%') & ' year-on-year'

Your final chart should look something like this:

Conclusion

I won't be using dynamic titling everywhere - it's a faff. But I think it can bring the hint of a human voice and some clarity to key charts.

Simplicity and accessibility are the marks of a great dashboard.

Say what you see and add it to your arsenal.

Google Sites

Report abuse