[Sequel to this post: Labeling Trellis Chart Anywhere]
To many people, the most difficult part of creating a trellis chart is to label it. Especially labeling it in the same sheet and with sparse data is even harder.
This week's #B2VB project prompted many to create trellis charts for the data set about unemployment in OECD countries.
Here I would use the same data set to show
- How to label a trellis chart in a single sheet.
- How to place the labels in a uniform way in spite of missing data.
To make it simple, we take three steps to explain how it works.
- Create a trellis chart without labels.
- Create labels only in a trellis.
- Integrate them via dual axis.
When you become familiar with the steps, you can start building from the 3rd one.
1. Creating a Trellis Chart
First let's create a trellis chart without labels.
The fields are as follows:
The Columns are calculated along Country Name.
2. Create Trellis Chart with Labels Only
Secondly, let's create a trellis chart to place labels only. All the labels are created regardless of missing data. You may notice from the previous chart that Israel and Turkey don't have data at the beginning months.
We add a new axis Window_Max(1). The table calculation is set to compute along Date.
The reason why we use a table calc Window_Max(1) for an axis, is due to the fact that for some country, data are missing for some of the dates. Window_Max(1) will fill those voids with value 1.
The labels are created as follows
This way, we only keep the first value of the windows calculation. Note that it is calculated along Date dimension.
3. Dual Axis
The third step is putting them together via dual axis. The labeling axis is fixed to be from 0 to 1. No syncing the axis please.
Here is the resulting trellis chart:
This is another example using the above technique https://twitter.com/aleksoft/status/1483572295373312001
Feel free to leave comments below or contact me at twitter @aleksoft.
Hi Alex,
ReplyDeleteHere's another question. Let's say that the label has a calculation. For example, I want to show an upward arrow, when the last record in the partition is greater than the running average. However, if the label is set up to appear only in the first record of the partition (because of the if statement, that says to show the label when first()=0) then the calculation run on the last record in the partition is excluded. Thoughts?