How to build an animated Bar Chart Race in Tableau Software

Most of us have encountered Bar Chart Race animations by the time you read me. Can we build it in Tableau? I would say YES and NO, It can be done but using the page shelf which is not available on the web versions of our dashboards.

Let me talk first about who inspired me to do this. John Burn-Murdoch, I invite you to follow him on twitter, deserves an award for building and sharing THIS:

The animation is so smoothed, catchy, I want to stay in it forever and ever. It invites us to know more about the rise and fall of those cities. And we can definetely hear a speaker voice behind this Bar Chart Race (Hans Rosling style).

In Tableau, we can use the page shelf to fake animation, each new value in the page field being a “frame”.

The page shelf

And we can use Tableau to display points, lines and polygons wherever we want! (Once we crack the code 🙂 )

Sooooo… we have everything needed to fake one! (and build anything we want in Tableau, but this is an other debate)

Data

We will use a main dataset with the information we want to animate. In the example, I have four fields: link, year, category and value.

“data” sheet

We will use a second dataset with two fields to build each bars: link, point.

“bar” sheet

And, that’s the new thing, we will use a third dataset to fake each frame.

“frame” sheet

If you want to show a race by year and have monthly measures too, you can try an alternative and better version of this by skipping this third dataset and use the month and year field to animate the viz!

In Tableau, join this three datasets on link as a cartesian product.

Calculations

What we want to do now is to display each bar at the right position regarding its ranking by year, for each value in category. We also want the bar to “move” from the current position to a next one.

Let’s begin the calculations!

anim_inter : usefull later for moving values

[Anim]/{max([Anim])}

frame : usefull later for page shelf (if you have a real field, like month, it’s better!)

[Year]+[anim_inter]

value_current : value for the current year

{FIXED [Year],[Category]:
min([Value])
}

value_next : value of the next year

LOOKUP(sum([value_current]),1)

value_inter : value moving linearly from the current to the next year (if you have real values, it’s better!)

sum([value_current])+
([value_next]-sum([value_current]))*min([anim_inter])

rank_current : rank of the current year

RANK_UNIQUE(sum([value_current]),”desc”)

rank_next : rank of the next year

LOOKUP([rank_current],1)

rank_inter : rank moving linearly from the current to the next year

[rank_current]+
([rank_next]-[rank_current])*min([anim_inter])

@x_inter : moving lenght of the bar

IF min([Point])=1 THEN 0
ELSEIF min([Point])=2 THEN [value_inter]1.0
ELSEIF min([Point])=3 THEN [value_inter]1.0
ELSEIF min([Point])=4 THEN 0
END

@y_inter : moving position of the bar

IF min([Point])=1 THEN [rank_inter]1.0
ELSEIF min([Point])=2 THEN [rank_inter]1.0
ELSEIF min([Point])=3 THEN [rank_inter]+0.5
ELSEIF min([Point])=4 THEN [rank_inter]+0.5
END

filter : to filter my top 10 if available

rank_unique([value_inter],”desc”)

label : display labels on my polygons (you can display what you want/need)

IF min([Point])=1 THEN min([Category])
ELSEIF min([Point])=3 THEN str(int([value_inter]/1000000))+”M”
END

This last step is not perfect. Who am I to know if my values move linearly a year to an other? That’s why it’s better if you use real values instead of my value_inter.

Visualization

Now it is time to visualize. You have to use @x_inter and @y_inter inf columns and rows shelves, frame in Pages, filter in Filters.

You have to use the Polygon Mark and use year, category, anim_inter in details, and Point in Path.

We will use the @y_inter again as a dual axis as a point Mark to display my labels.

You won’t see the bars because we need to configure the table calculations.

@x_inter will be computed as Year, and frame.

@y_inter will be computed as Category for rank_current, by Category, Year and frame for rank_next and restarting every Category.

@filter will be computed as Category for filter, Year and frame for value_next.

And… TADAAAA, a Bar Chart Race in Tableau using the page shelf commands.

page shelf commands.

I updated my first dataset to show information about population by countries by year and whitout modifying anything else:

35 Comments

  1. Hi,

    I tried to replicate this visualisation with my own data, similar structure (Month, Country and Sales) as in your example but I have trouble with these calculations:
    1. rank next – I cant get it to return the actual rank for the following month. I tried all possible options but it either gets me the rank for the country row below or ranks a country by sales across all months, without considering rest of countries.
    2. @x_inter and @y_inter – i cant repeat adding 1.0 after [value_inter] – tableau treats it as error in the formula

    Can you help?

    Xenia

    Like

    1. Hey Xenia! If you have troubles with the calculation, I recommand you to go step by step 🙂 Using intermediate worksheet for example. About 2., I think this is a data type error, you have to make sure every value in other conditions are float, by multiplying by 1.0 for example!

      Hope it helps.

      Ludovic

      Like

      1. I tried to reproduce by steps but its confusing with actual data. Also couldnt understand the logic. Can you please explain the steps with reason in depth or point to an appropriate source to understand it.
        Thanks

        Like

      2. Hey Mayur! Sorry I was a bit short in time for this one. The main principle to get is that an animation is built thanks several pictures or frames. The main goal is to build those frames then. Each bars are moving so we need to calculate each positions, and use the “Pages” functionnality to animate it.

        Like

  2. Hi Ludovic,
    Amazing job! I nearly replicate this but I stuck with labels. I cant understand how you separate them(first with “Category” is over the bar, and second with “Value” is at the bar end) Could you give me any advice?

    Like

    1. Oh sorry Lukasz, it’s not clear in my blogpost. You have to use the filed “label”: display category on point 1 and value on point 3 🙂

      Like

    1. Bonjour Mohamed, de quelle calculs parles-tu ? Les techniques utilisées ici peuvent être assez complexes à comprendre et à reproduire pour des nouveaux utilisateurs Tableau 🙂

      Like

  3. This is AWESOME! Thank you for the excellent instructions–I’m not very advanced at all and I was able to follow the concepts and instructions. I only have one question: the animations shown on this page are movies, right? Not embedded Tableau vizzes?

    Like

  4. Hey Ludovic Great post !!
    Explanation is neat and clean. I am able to replicate it successfully.
    Just one question why you have used only 4 point. Whats the significance of it ?
    Again thanks for contributing to community.

    Like

  5. Hi Ludovic, thanks for posting this as i have been trying to create one of these for a while. I have replicated it to an extent but i am struggling with a few points: 1] i cannot seem to be able to create the bars and 2] one of my high value points seems to be locked at position 1–the value is 2.9m and should rank higher but is static at the first point. Any ideas? I have 29 categories[ subjects]. Thanks. Greg

    Like

  6. Hi Ludovic, I figured out what i needed to, again thanks for the adding in your workbook for guidance. The next question is, it seem Tableau Server does not support the ”play” function, so how can i get around this and allow for publishing even if in loop back mode?

    Like

    1. Hi Greg, one of the solution would be to create a GIF image of your work and use a webpage block in your dashbaord linking to the GIF 🙂

      Like

  7. salut Ludovic, c ‘est vraiment super ton tutoriel, j’ai essayé de refaire pareil mais j’ai aucune visualisation, j’ai fait un copie-coller de ton tuto mais j’ai des valeurs non définies partout et par conséquent aucune visualisation, j’avoue que je débute avec Tableau, ce serait vraiment cool que tu me donnes un coup de main. merci d’avance

    Like

    1. Bonjour Charles, je pense que ma réponse va te décevoir mais si tu débutes avec Tableau, ce n’est pas la chose la plus simple par laquelle commencer 😦 J’utilise des notions de Data densification et des fonctions de calculs de table assez avancées pour parvenir à ce résultat. J’espère que ça ne te découragera pas pour le reste de ton apprentissage !

      Like

  8. merci Ludovic, pour ta réponse. je suis juste un peu déçu, lol, Pour tout dire, mon objectif était plus de trouver un Outil pour les bar chat race. Je n’ai connu Tableau ( que je trouve très pratique pour l’étudiant en maths que je suis) que par le biais de ton tuto, ce qui explique le fait que j’ai un peu du mal a men accommoder. jetais plutôt dans un registre Windows notamment avec Power BI ou j’essayais sans succès a laide d’un outil graphique de réaliser des bar chart race, c’est donc avec un peu de hasard que j’ai voulu imiter ton tuto dans Tableau. J’ai eu un peu de mal au début mais a laide de quelque tutos , j’ai pu comprendre les bases de manière a pouvoir corriger les erreurs de syntaxe que ton tuto comportait pour passer des calculs incorrects a des calculs corrects en terme de syntaxe, ce qui du reste demeure insuffisant vu que ces calculs valides aboutissent a des cellules avec des valeurs non définies, j’en ai déduit que je n’avais visiblement pas le paramétrage ou le prérequis nécessaire pour utiliser ces fonctions avancées, mais je suis néanmoins disposé à apprendre ce qu’il faut pour y parvenir, pour cela je
    dois donc uniquement apprendre la data densification si j’ai bien compris ?

    Like

  9. Bonjour Charles, merci de ton retour. Si ta volonté est uniquement de créer du bar chart race, peu importe l’outil, je te conseille d’utilise https://app.flourish.studio/@flourish/bar-chart-race

    Pour le reste, je serai intéressé par connaître les erreurs de syntaxe du tutoriel 🙂 Concernant l’apprentissage de Tableau, une prise en main pas à pas de Tableau me semble nécessaire avant d’aller vers la data densification. Créer du graphique usuel, comprendre comment fonctionne les agrégations, les calculs de tables, les LODS et les utiliser sur des cas classiques. Puis si les graphiques non natifs t’intéressent, comprendre pourquoi je dois ajouter de nouveaux points et comprendre comment je peux le faire grâce à la data densification. Enfin comment combiner toutes ces théories pour pouvoir positionner tes points de manière générique et automatique pour construire des graphiques beaucoup plus avancés (comme le barchart race). Si tu veux quand même reproduire le barchart race rapidement sous Tableau, je te conseille de télécharger le workbook et d’essayer de remplacer les données d’entrée: https://public.tableau.com/profile/ludovic.tavernier#!/vizhome/PopulationRace/PopulationRace

    Like

    1. Merci Luc pour le workbook et sorry pour le retard, un peu oberbooked. J’ai comme tu me l’as conseillé remplacer les données sources par les miennes. j’ai ensuite de façon assez prévisible perdu la validité des calculs. Problème selon moi lié, d’une part a l’inexistence dans mes données de certaines dimensions (point et anim ) et d’autre part à un défaut de produit cartésien. j’ai donc eu à ajouter le produit cartésien de la feuille data, frame et bar ainsi que les dimensions manquantes. Concernant Le produit cartésien , si j’ai bien compris il s’agit de lier ces trois feuilles par link. Mais Quand à l’attribution des valeurs anim, elle reste pour moi difficile à cerner. J’ignore comment les valeurs sont attribuées. Alors j’ai un peu arbitrairement fixé des valeurs semblables. 0a10 pour anim et 1 partout pour point, pour essayer de comprendre le mécanisme. (Voir l’effet sur le visualisation et attribuer les valeurs qui siéent) . Mais j’y arrive toujours pas. 😢. Mais je continue néanmoins à apprendre les bases de tableau.

      Like

  10. Hi, Ludovic. Although I understand the main concepts of the viz, I’m struggling with a few fundamental things.

    The movement in the viz is created by cycling through “frame”. The movement of the polygon’s length and its vertical position depends on “value_current” and “value_next”. “value_next” is a table Calc (Lookup of “value_current”).

    My first struggle is that I imagine “frame” as a filter alike. On an individual page (“frame”), how would a table calc be able to function?

    While trying to find a explanation, I dug into your workbook and ran into a second struggle. When I lay side by side, Category, anim_inter, Year, and frame, the behavior is as expected. Picking a single row, I could have for example: Category = Algeria, anim_inter = 0, Year = 1960, Frame = 1960.0. However, to my surprise, when I bring in “value_next” (table calc), with Category = Algeria, anim_inter = 0, Year = 1960, the associated “frame” expands to 11 instances from 1960.0 to 1961.0. Vaguely, I can attribute this behavior to the fixed LOD (“anim_inter”) being {} (at all levels). But I can’t concretely form an explanation. Could you help me out?

    Thanks,

    Swang

    Like

  11. Hi Ludovic,

    This is awesome. I couldn’t access the raw datafile via the github link at the top of the post. However I do have the twb file you posted.

    Can I confirm:
    The data sheet has a value of only 1 in the Link field? I assume this is renamed to ‘Number of records’ either by Tableau or yourself – I exported the data from your twb file and there is no Link field, just a Number of records field. Can I also confirm the bar sheet only has Link value of 1 and Point values 1,2,3,4. ie. What you have added in the screenshot is the entire contents of that sheet? And similarly for the frame sheet, it’s just those values in the screenshot which make up the entirety of the contents of that sheet?

    Probably a stupid question and I’m pretty sure the answer is yes but I didn’t want to just assume it was the case.

    Do you also happen to have the original csv handy so I can try to replicate the Cartesian product link in Tableau? I have never used that type of linkage to join data in Tableau, (typically just use unions and joins) so I’d like to play around with it.

    Thanks in advance and thanks so much for sharing!

    Cheers,

    Pete

    Like

  12. Hi,Ludovic Tavernier nice article,
    i have a small problem while joining the data in tableau can u guide me how to do cross joins and all the calculated fields are showing up nulls,please help me with this.
    thank you

    Like

Leave a Reply to Marie-Jeanne Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s