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”.
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.
We will use a second dataset with two fields to build each bars: link, point.
And, that’s the new thing, we will use a third dataset to fake each frame.
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.
I updated my first dataset to show information about population by countries by year and whitout modifying anything else:
Super article merci Ludovic 😉
LikeLiked by 1 person
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
LikeLike
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
LikeLike
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
LikeLike
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.
LikeLike
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?
LikeLike
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 🙂
LikeLike
Hi Ludovic,
Amazing Job. Could you explain how to separate labels?(First one is over the bar and second one is at the end)
LikeLike
Hey Lukasz, did you get the answer to your question?
LikeLike
Yes, thank you. I am sorry for double post.
LikeLike
can you please attach your workbook.
LikeLike
Hey Harsh, you can find the workbook here: https://public.tableau.com/profile/ludovic.tavernier#!/vizhome/PopulationRace/PopulationRace
LikeLike
Merci Ludovic pour ce tutoriel, je suis nouveau sur Tableau comment on fait pour appliquer ces calculations ?
LikeLike
Bonjour Mohamed, de quelle calculs parles-tu ? Les techniques utilisées ici peuvent être assez complexes à comprendre et à reproduire pour des nouveaux utilisateurs Tableau 🙂
LikeLike
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?
LikeLike
Hello Julie! THank you for you comment 🙂 Yes it’s a gif 🙂 Not possible to use pages on web yet 😦
LikeLike
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.
LikeLike
Hi umang! Thank you 🙂 4 points for the 4 point of a rectangle 🙂
LikeLike
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
LikeLike
just one other Question–do i need to use Tableau Prep for this or will a normal join suffice? thanks. Greg
LikeLike
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?
LikeLike
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 🙂
LikeLike
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
LikeLike
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 !
LikeLike
existe t-il un tuto video?
LikeLike
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 ?
LikeLike
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
LikeLike
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.
LikeLike
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
LikeLike
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
LikeLike
Hi Ludovic,
you have missed the multiplication sign (*) in your example for @xinter and @yinter.
LikeLike
Hi Ludovic Tavernier, how to join this tables i am getting null values for all my calculated fields.
please help me with this.
LikeLike
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
LikeLike