In this post we take a break from designing charts and instead take a detailed look at the expressions used by Charticulator when you bind data to an attribute. I’m talking about this type of thing:-
All Charticulator’s expressions are introduced by the dollar ($) sign. This is synonymous with the equals (=) sign in DAX or Excel. But what is different from DAX or Excel formulas is that in Charticulator part of the expression can include the format specifier. Also a field is referenced without a qualifying table name.
Understanding these aspects of the Charticulator expression means that we can focus on these three separate elements:-
- Referencing a field name
- The aggregation of the data
- The format of the resulting values
Referencing Field Names
When you want to reference a field in Charticulator, you can simply use the field name. However, if the field name has spaces in it, you must surround the field name with grave a character. The grave character is situated top left of your keyboard, to the left of “1” and under “Esc”.
Binding a categorical field to the “Text” attribute of a Text mark
You can concatenate field names in a Text mark by using the plus (+) symbol
Using Field Names on a Numeric Axis
You may remember that in my last blog post, we looked at using “Tick Data” on a numeric axis where you reference a field name that will label the axis. This is one occasion where you need to type the expression into the attribute. So for instance, below we’ve concatenated the Wine field and the PricePerCase field, separated by a space. Notice that you don’t need any aggregating function because the aggregation is already being expressed in the “Data” attribute:-
In the Tick Data attribute you reference the field name.
Using Charticulator’s Aggregate Functions
Just like Power BI measures, Charticulator supports functions that return scalar values such as “sum”, “avg”, “max”, “min”, “count”, “stdev”, “variance”, “median”, “first”, “last”. When you first bind a numeric field to an attribute, Charticulator will use the “avg” function by default. For instance, we’ve bound the Sales field to the “Height” attribute of a rectangle shape.
Using this data…
…and binding Sales to the “Height”
This is because, unless you use the “Group by..” attribute, each shape represents a single row from the underlying data. Therefore expressing the average of one value is that value (as indeed would using “sum”, “max”, “min”, “first” or “last”). In this case, there is no advantage to changing the function. The exception to this is when you group your data using “Group by…”
If you are using the “Group by…” attribute of the plot segment then you can edit the function according to what calculation you want to plot. For instance below, we’ve grouped by the Year field and then changed the function used by the “Height” attribute to “min”.
You can group the data….
….and change the aggregating function
To thing to note here is that these are aggregations of the data behind the visual, not aggregations of the source data:-
In upcoming blog posts, I’ll show you how you can use the “Group by…” attribute to great effect in your chart designs.
In the grouping example above where we changed the function to “min”, notice that the Text mark has also been edited to reflect the function being used by the “Height” attribute.
Expressions using Categorical Data
Let’s start with the text expression that is used when you bind a categorical field to an attribute. For instance, we’ve added the SalesPerson field to the “Fill” attribute of a rectangle mark. When using categorical data Charticulator uses the “first” function by default.
You can compare this behaviour to what happens when you use a text column in a Card visual in Power BI:-
Charticulator must use a function to retrieve the text value. As we already know unless we’re grouping the data, the glyph will represent one single row from the underlying data so “first” or “last” will work.
Formatting Numeric Expressions
When you bind numeric data to a Text mark Charticulator will apply a default numeric format using one fixed decimal place, indicated by the format string inside the curly brackets:-
You can find more information regarding this syntax here https://github.com/d3/d3-format. But for the moment I’m just going to concentrate on what we need to know to format our numeric data in Charticulator.
Specify Number of Decimal Places
So for instance, if we take the format specifier in the “Text” attribute circled above i.e. “.1f” , this translates as :-
[type] = “f” for fixed decimal. You can find the other “types” in the documentation on GitHub.
[.precision] = “.1” for 1 decimal place.
So this is the expression for 0 decimal places:-
Add a Comma Separator
If you need a comma separator and for instance zero decimal places this would be the format:-
Using Currency Format
For this, just type the currency symbol at the start of the expression.
Use M or K
If you want to format using M for millions or K for thousands use the type “s” like this:-
Rounding to Significant Digits
Use the type “r”.
Format as Percent
To format as percent and multiply the value by 100, use the type “%”.
Formatting Numeric Axes Tick Labels
You can use any of the above format specifiers to format a numeric axis. Use the “Tick Format” attribute and type the required format into the attribute. Here are some examples:-
I appreciate that all of this has been a bit dry. Nevertheless it’s very frustrating if you don’t know how to format your data so at least now you won’t have that problem.
I know that you’re itching to get back to creating charts. However, In order to move forward with more challenging charts, we need to get to grips with the rather odd world of Charticulator’s Scales and Legends, which are the subject of my next post. I know we’ve already been using Legends and we’ve touched on the workings of the Scales pane but like always with Charticulator, there’s still a lot more to say. I don’t think you’ll find that very surprising.