Category: Charting

Dynamic text

Introduction

By using Dynamic text in charts, you can present data from time series as part of any text that you enter. For instance, you can have a textbox with a text like The last value is 12.72, where the number 12.72 comes from a time series and is automatically updated.

The part of the text that is dynamic is written within curly brackets. The example above might have been written like The last value is {s .Value}. The text within the brackets will be processed and replaced with the result in the output.
You can have any number of dynamic texts in a text. For example: The value at {s .Date} is {s .Value}, which could result in something like The value at 2014 Q1 is 12.72.

The Dynamic text editor helps you to type the dynamic text expressions. You get feedback suggestions as you type. Tooltips helps you examine the expressions.

The general format is {x parameters}. The first letter after the opening bracket, called x determines the type of dynamic text. There are two types of dynamic texts: s for series data and c for chart and document data.

You can, and sometimes need to, put quotes around the parameters. In particular, you need to do this if a parameter contains space characters.

There is detailed documentation below, but here are some examples:

{s .Value} Present the current value from the current time series with automatic formatting.
{s .Value F2} Present the current value with two decimals.
{s .Value  #,,} Divide the value by 1 million and show without decimals.
{s .Value  #,0,,} Millions with thousand separator and no decimals.
{s .Value[2012] A3} Format the value of 2012 using three significant digits.
{s .Value[-1Y]} The value one year back.
{s .Date} Present the current date from the current time series with automatic formatting.
{s .Date D} Present the date with the long date format.
{s .Region} Present the region of the current time series.
{s s1.Currency -m} Present the currency of the specified time series. -m means that there will be no error if the series has no currency.
{c DocumentSources –d “\n”} The sources of the document separated with a new line.
{c DocumentLastDataRevision f -p "Data last updated " -m} Shows when data was last updated. Example: Data last updated 28 February 2024 01:30 (Feature is available in Macrobond 1.28 and later.)

If you want to include a bracket as part of your text, you need to write double brackets: {{ will result in { in the output and }} will be }.

Default series and observations

When a dynamic text is processed there is a set of default series and a default observation that depends on the context where the expression is used.

Element Series Observation
Main/subtitle All series used in the chart The last observation
Copyright text All series used in the chart The last observation
Legend item The series used for the graph The last observation
Y-axis title The series plotted against the axis The last observation
X-axis title The series used in the pane The last observation
Text box The series used in the pane The last observation
Observation label The series used for the graph The observation it points to
Axis value label The series it references The last observation

This means that a dynamic text like {s .Value} can sometimes refer to several series and this several values. By default, such a list will be presented as a list separated by comma. For instance, when used in the main title and the chart uses two series the text might be 12.34, 56.78. If you just want one of these, you should consider referencing a specific time series or using the index operator.

The .Date property works in the same way and produces a list of dates.

All other properties, like .Region, will remove any duplicates from the list.

Series data

The dynamic text is written on the format

{s series.Data[index] format options}

The options, format, data, and index parts are always optional. The series part is optional if there is a default series in the context where the text is used.

There are several options available:

-f format The format depends on the data type as covered below.
-d delimiter The delimiter is used to when there is a list of items. The default delimiter is ' , '. For new lines you can use '\n'.
-p text A text that will be prepended to the formatted text.
-s text A text that will be appended to the formatted text.
-m text A text that will be displayed of the specified data property or data point is missing. By specifying –m without a text, there will be no text at all in the case where the property is missing.
-ct text Instead of formatting the value of the data, the specified text will be written if the data is 'true'. The value is 'true' if it is a Boolean value that is True or a number >= 0.5. If the value is “false”, the text specified by –cf will be used if available and otherwise empty.
-cf text Instead of formatting the value of the data, the specified text will be written if the data is 'false'. The value is 'false' if it is a Boolean value that is False or a number < 0.5. If the value is 'true', the text specified by –ct will be used if available and otherwise empty.

You can thus specify the format either as the second part of the dynamic text or as an option.

series

The part series is a reference to the series on the format s1, s2, s3 etc.

This will reference the current value of the series s1:

{s s1}

The reference can be omitted if there is a default series in the context where the text is used. In this case the dynamic text can be as short as

{s}

.Data

The part .data specifies what data property to retrieve for the specified series. The default value is .Value, which is the vector of values.

Examples of data:

.Value The values of the series. You can specify an index as point in time. If no index is specified, the index of the context will be used, which is often the last value.
.Date The dates of the series. You can specify an index as point in time. If no index is specified, the index of the context will be used, which is often the last value. This property does not exist for category series.
.ValueLabel The value labels of the series. You can specify an index as point in time. If no index is specified, the index of the context will be used, which is often the last value.
.Ordinal The ordinal of the series. You can specify an index as point in time. If no index is specified, the index of the context will be used, which is often the last value. This property only exists for category series.
.GraphText The text of the associated graph.
.DisplayUnit The unit. (in Dynamic text roll search for 'Axis label')
.Currency The currency.
.OriginalCurrency The currency of the original series before any currency conversion.
.ObservationDate When 'Last common' or 'Value at' methods are used in analysis it shows date of the observation.
.OriginalEndDate The 'Original end date', 'Original start date', 'Original frequency' and 'Original currency' refers to the original values before any transformations or calculations.
.Frequency The frequency.
.FullDescription The full description of the series.
.Region The country or region. There can be several regions. All regions will be included if no index is specified.
.Release The title of the release associated with this series. This is an entity with additional data.
.Release.LastReleaseEventTime The time of the previous release of new data.
.Release.NextReleaseEventTime The time of next scheduled release of new data.
.Database The database where the series is stored. For example: 'Macrobond', 'Account in-house' or the name of SQL database. 

Some examples of usage:

{s .Value[2012]} The last value of 2012 of the default series.
{s .Value[-1]} Second row of values in a category series.
{s s2.Value[-1y]} The value one year before the end of the series s2.
{s s2.Date} The last date of the series s2.
{s s2.Date[-1q]} The date one quarter before the end of the series s2.
{s .Region} The regions for the default series.
{s s2.Region[0]} The first region of the series s2.

Format

The format is optional, and the default format depends on the context and data type.

Formatting numbers

The following predefined number formats are defined:

Fn Fixed-point notation where n specifies the number of decimal digits. Example: 1234.5678 formatted using 'F2' is '1234.56.'
En Exponential notation where n specifies the number of decimal digits. Example: 12345678 formatted using 'E2' is '1.23E+007.'
Gn Picks the shortest of the F and E formats with the specified number of significant digits. Example: 12345678 formatted using 'G3' is '1.23E+07.'
Pn Percent notation where n specifies the number of decimal digits. The value is multiplied with 100 and displayed with a % symbol. Example: 0.1234 formatted using 'P1' is '12.3 %.'
An Automatic scaling by using n significant digits and an exponent divisible by 3, excluding -3 and 3. Example 1234567 formatted with 'A3' is '1.23 million.'
Nn Same as Fn, but formats numbers using a thousand separator.
0,, Shows value in millions or billions.
#,0,, Shows value in millions or billions with thousand separator.

The decimal separator is always localized.

The formatting string can also be composed of the following specifiers:

0 The '0' format specifier serves as a zero-placeholder symbol. If the value that is being formatted has a digit in the position where the zero appears in the format string, that digit is copied to the result string; otherwise, a zero appears in the result string. The position of the leftmost zero before the decimal point and the rightmost zero after the decimal point determines the range of digits that are always present in the result string. Example: The number 1.2 formatted using '00.00' is '01.20'.
# The '#' format specifier serves as a digit-placeholder symbol. If the value that is being formatted has a digit in the position where the pound sign appears in the format string, that digit is copied to the result string. Otherwise, nothing is stored in that position in the result string. Note that this specifier never displays a zero that is not a significant digit, even if zero is the only digit in the string. It will display zero only if it is a significant digit in the number that is being displayed. The number 1.234 formatted using '#.##' is '1.23'.
. The ' . ' format specifier inserts a localized decimal separator into the result string. The first period in the format string determines the location of the decimal separator in the formatted value; any additional periods are ignored. Example: The number 1.234 formatted using '0.00' is '1.23'.
% A percent sign (%) in a format string causes a number to be multiplied by 100 before it is formatted. The localized percent symbol is inserted in the number at the location where the % appears in the format string. Example: The number 1.234 formatted using '#%' is '123%'.
, The “,” format has two functions:

  1. Thousand separator: If a comma is specified between two digit placeholders (0 or #) that format the integral digits of a number then a group separator character is inserted between each number group in the integral part of the output. Example: using the format '#,#' and US number format settings, the number 1000000 is formatted as '1,000,000'.
  2. Scaling: If one or more commas are specified immediately to the left of the explicit or implicit decimal point, the number to be formatted is divided by 1000 for each comma. Example: using the format '#,,' , the number 1000000 is formatted as '1'.

The two usages of ' , ' can be combined. Example '#,0,,' scales the number by 1000000 and uses a thousand separator.

Rounding is always made away from zero.

Instead of the formatting specifiers, you can also specify a formatting group:

@groupName:digits

All numbers that belong to the same group will be formatted with the same number of decimals so that at least the specified number of significant digits are included. If no :digits part is included, a default value of 3 will be used.

There are three special groups: YAxisMain, YAxisAlternate, and XAxis. These will be used as the default group when the default series is used, and the context can be associated with an axis.

Some examples of usage:

{s .Value F2} Formats '123.456' with two decimals like '123.46'.
{s .Value A4} Formats '123.456' with four significant digits like '123.5'.
{s .Value P1} Formats '0.123456' with one decimal digits like '12.3 %'.
{s .Value #,,} Formats '123456789' scaled by 1 million and no decimals '123'.
{s .Value #,,,.00} Formats '123456789' scaled by 1 billion and two decimals '0.12'.
{s .Value -f F2} This is a different way to write the same thing as the first sample.

Formatting dates and time stamps

Predefined date patterns that format according to the current language settings:

Q Year and quarter. For example, 2008 Q2.
W Year and week. For example, 2008 W21.
G A short date with time. Example for US: 4/23/2008 1:45 PM.
d A short date. Example for US: 4/23/2008. This is the default.
D A long date. Example for US: Thursday, April 23, 2008.
f A long date with time. Example for US: Thursday, April 23, 2008 1:45 PM.
M Display day and month. Example for US: April 23.
Y Display month and year. Example for US: April, 2008.
t The time. Example for US: 1:45 PM.

A format string can also be composed of two or more of the following specifiers:

d The day from 1 to 31. When used as the only specifier, it must be written as %d.
dd The day from 01 to 31.
ddd The abbreviated name of the day of the week. Example for US: Mon
dddd The full name of the day of the week. Example for US: Monday
M The month from 1 to 12. When used as the only specifier, it must be written as %M.
MM The month from 01 to 12.
MMM The abbreviated name of the month. Example for US: Jun
MMMM The full name of the month. Example for US: June
yy The year without century. Example: 95
yyyy The year including the century. Example: 1995
: Time separator. Example for Italy: .
/ Date separator. Example for Sweden: -

If you use spaces in the formatting specification, you need to quote the parameter.

Chart data

The dynamic text is written on the format:

{c data format options}

There are two options available:

-f format The format depends on the data type as covered below.
-d delimiter The delimiter is used to when there is a list of items. The default delimiter is ' , '. For new lines you can use '\n'.

You can thus specify the format either as the second part of the dynamic text or as an option.

The following data items are available:

DocumentName The name of the document.
AutomaticTitle The automatic title of the chart.
AutomaticUnitYAxisMain The automatic of the main y-axis.
AutomaticUnitYAxisAlternate The automatic of the alternate y-axis.
AutomaticUnitXAxis The automatic of the x-axis.
DocumentDatabases List of databases from which time series used in given document originate. Values might include Macrobond, names of In-House sources, names of 3rd party connectors like Bloomberg.
DocumentSources A list of the sources of the time series used in this document.
DocumentSourceAbbreviations List of abbreviations for the data sources of the time series used in this document
DocumentLastDataRevision Shows when data was last updated. (Feature is available in Macrobond 1.28 and later.)
Now The today’s date.

Some examples of usage:

{c DocumentSources –d “\n”} The sources of the document separated with a new line.
{c DocumentLastDataRevision f -p "Data last updated " -m} Example: Data last updated 28 February 2024 01:30 (Feature is available in Macrobond 1.28 and later.)
{c Now} The current date.

Example

Axis value labels

Axis & Scale

How to:

How to add an alternate axis (scale) to the chart?

There are two ways to add an alternate axis to a chart:

  • Click on a graph in your chart, in the tab presentation properties tick the checkbox 'Use alternate scale'

  • Right click in the graph area, choose Graph layout, and tick the checkbox for 'Alt. scale'

How to add thousand separator to y-axis?

It is possible to use optional Thousand separator setting for the axes in the charts. It adds a separator after every third digit for numbers larger than 1,000.

There are two ways to use thousand separator:
  • from Presentation properties

There is a Thousand separator setting for the axes in the charts. It adds a separator after every third digit for numbers larger than 1,000. You can tick Thousand separator option under Format > Language.

  • from Dynamic text

It is also possible to use it in Dynamic text window

How to change axis title?

To rename units of the y-axis, click on the area right above the top of the axis. This will result in 'Properties for Axis title' to be shown in the Presentation properties group box. Then, click on the Edit button next to 'Axis title' and input your desired unit name. You can also use Dynamic text. It will only change name of y-axis, the units will remain the same.

How to change chart scale?

You can adjust the chart scale by accessing 'Properties for Value axis' in the Presentation properties tab by clicking on the y-axis. Here, the Scale section is where you can modify the scaling. You can define the Display range and Step length. Reverse the scale and Logarithmic scaling are also available options.

Click on the More button to access further options: Scaling where you can choose in which scale the y-axis will be presented and Decimals which modifies decimal places.

How to change step length?

You can define the step length for scale of y-axis (or both if it's a Scatter chart).

How to change x-axis' labels?

On Time chart

By default the 'Automatic' option is enabled which shows full information for given frequency. You can modify this by clicking on x-axis and going to Presentation properties > Labels.

On Category chart

By default it will show dates/weeks/months (depends on the frequency). You can go change them to numbers. To do this click x-axis and under Presentation properties > Labels uncheck 'Show value labels'.

Workaround - Covering labels

See this page for workaround with which you can replace/cover labels on x-axis.

How to change the scale of y-axis when working with large numbers?

When dealing with series expressed in large values, for instance billions or trillions, you can use a scientific notation for decimal precision instead of manually typing the whole number.

Let’s say you have a series that is expressed in trillions and would like to change the range of the Y-axis scale, so that it reaches 10 trillion. Instead of typing '1000000000000' in the Display range box, you can type '1e12.' The Scientific E-notation is written as: 'xey' (x times ten raised to the power of y). 70 000 000 can therefore be written as 7e7. It also works with decimals (e.g. 1.5e9, meaning 1,5 billions).

How to set a logarithmic scale?

Click on the y-axis and open the Presentation properties tab at the top of the window. In the Scale group, check the “Logarithmic” option.

You cannot set logarithmic scale from 0 as the logarithm of zero is not defined.

How to show last year label on x-axis?

This feature is available in Macrobond 1.27 and later.

Displaying years on x-axis is automated and it might happen that when not every year is visible the last shown year is actually a few years in the future/past. To prioritize label for the last year available in a series mark 'Prefer last year' box.

How to show year above month on x-axis?

By default, on x-axis month is shown above year. You can use setting - Spacing - with Distance, to change the order. Set Spacing to negative value to move years above months.

How to synchronize axes when adding alternate scale?

Using the Synchronize settings will affect the way in which the alternate scale is displayed. Below we go over the outcomes of combing these settings

  • Alternate Scale + Synchronize: None
    Each axis has an independent scale and independent grid.
  • Alternate Scale + Synchronize: Grid
    Each axis has an independent scale, but both axes will share the same grid. You can’t adjust the step length of the axes, only the scale range.
  • Alternate Scale + Synchronize: Values
    Each axis will share the exact same scale and grid, which will void the outcome of applying an alternate scale.

How to use grading on y-axis?

For certain time series, you can match the values of the y-axis to grading labels (i.e., AAA, AB+). Data from source's which use grading will automatically use this setting, but you can set it by yourself too. It can be accessed after marking y-axis.

How to use 'Reverse' with 'Auto'?

Some series contain metadata that they are best presented using a reversed value axis. By default series are set to 'Reverse: Auto' to take that metadata into consideration.

Colors & Patterns

How to:

How to change the color of a series’ graph in a chart?

Click on the series’ graph and open the Presentation properties tab at the top of the window.  In the Appearance group, you can select a graph color from the graph style drop-down list or enable more color options by selecting custom graph style.

If you want to re-use the selected graph’s color, you can save it as part of a Style sheet, which you can easily apply to your charts.

How to change the background color of a chart?

You can adjust the background color of what we call the graph area, the area within the axes where your series are represented.

Click on the graph area background (avoid the graph itself and the grid lines), and open the Presentation properties tab. In the Appearance group, you can select the color from the drop-down list or create a custom color.

If you want to re-use the selected background color often, you can pin it to the drop-down list or save it in as part of a Style sheet that you can easily apply to your charts.

How to set the pattern?

Select graph type where you can use pattern, for example Area charts. Click on area, open color's editor, and select pattern.

Bubble chart

For elements not mentioned here see Elements of the chart.

Overview

Bubble chart is graph type which displays three dimensions of data. It can be considered as variation of scatter chart where data points are replaced with bubbles.

What this type of chart needs?

This graph type needs three series, as the 1st series in the list is applied to the X-axis, the 2nd to the Y-axis and the 3rd series will be used to define the size of the bubbles.

Settings

Size of bubble

To adjust the size of the bubbles, click on one of them. You’ll find the size settings under the presentation properties tab, here. The value is given in pixels. This number will set the size of the biggest bubble (highest value in series), all others will be adjusted proportionally.

Reference value

If this field is empty the 'Size' from field above it (representing bubble width in px) will be assigned to the largest value.

If you type in for example '50' the bubble will have width as stated in 'Size' when the value in series is '50', all others will be adjusted proportionally.

Negative values

By default, bubbles with negative values are not visualized. Simply mark the check box to include them. When negative bubbles are visualized the size of the bubble is defined by the absolute value of the series.

How Graph layout works?

Graph layout window allows to choose one of graph styles which differs depending on chart type. There are three ways to access Graph layout settings:

  • by clicking on Graph layout icon

  • by right clicking on the chart and selecting Graph layout option

  • by using the keyboard shortcut CTRL + L

Here, you can define how the graph should be displayed as well as which series corresponds to which axis.  On the right side of the window, the graphed pairs of series are displayed under a graph type. The first series of each pair is placed on the x-axis and the second one on the y-axis. Click and drag to move them if you want to switch the axes.

Examples

Bubble chart sample

In this example we presented typical use of Bubble chart.

New houses construction

Bubbles can be also used as weights on Time chart.

Open-high-low-close & Candlestick chart

For elements not mentioned here see Elements of the chart.

Overview

Open-high-low-close

This type of graph, called also OHLC is typically used to illustrate movements in the price of a financial instrument over time.

Candlestick

This is simply other type of OHLC graph, where each candlestick represents all four important pieces of information for that day: open and close in the thick body; high and low in the 'candle wick'.

What this type of chart needs?

To create Open-high-low-close or Candlestick graph you need 4 series which will represent movements of price - specifically open, high, low and close. Without it it's not possible to create these graphs.

How Graph layout works?

Graph layout window allows to choose one of graph styles which differs depending on chart type. There are three ways to access Graph layout settings:

  • by clicking on Graph layout icon

  • by right clicking on the chart and selecting Graph layout option

  • by using the keyboard shortcut CTRL + L


Here, you can define how the graph should be displayed as well as which series corresponds to which axis.  On the right side of the window, the graphed pairs of series are displayed under a graph type. The first series of each pair is placed on the x-axis and the second one on the y-axis. Click and drag to move them if you want to switch the axes.

Example

Graph samples

In this example we presented available types of charts.

Stripe, Range & Fill chart

For elements not mentioned here see Elements of the chart.

Overview

These three graph types transform series included in chart respectively into:

Stripe

If there's two series included, stripe will treat it as start and end of range, and it will show area in between. Area displayed in Stripe chart type have uniform manner.

Range

When this graph type is chosen, it will transform all series included in the chart into range. At first glance it looks like Stripe, however in reality it's a set of columns, which is visible when we limit range on x-axis. Columns in range chart starts from the minimum and ends with the maximum value. This way each column highlights the difference between the highest and lowest values.

Fill

Fill type of graph needs specific type of series, otherwise when we choose it from Graph layout - it would make chart unreadable. Typically, it would be a series which represents Business Cycle Reference Dates. This is also another way for adding Fill range to chart, for more about this check Recession bands (Fill range).

What this type of chart needs?

Similarly, as any other graph type, those graph types needs any series added in Series list. However, in case of Stripe and Range, there must be at least two series included, as it need to specify the beginning and end of area. Regarding Fill graph type, ideally series need to be constructed based on simple numbers, like 0 and 1.

How Graph layout works?

Graph layout window allows to choose one of graph styles which differs depending on chart type. There are three ways to access Graph layout settings:

  • by clicking on Graph layout icon

  • by right clicking on the chart and selecting Graph layout option

  • by using the keyboard shortcut CTRL + L


Here, you can define how the graph should be displayed as well as which series corresponds to which axis.  On the right side of the window, the graphed pairs of series are displayed under a graph type. The first series of each pair is placed on the x-axis and the second one on the y-axis. Click and drag to move them if you want to switch the axes.

Example

Graph samples

In this example we presented available types of charts.

Area & Stacked area chart

For elements not mentioned here see Elements of the chart.

Overview

Area

This type of graph is based on the line chart, with the area below the line filled in with a certain color, this also includes negative values. Range is defined by each point of time series.

Stacked area

Similarly, to Area graph type, it shows whole range of series. However, it displays all series stacked on top of each other. Note that this graph type does not include negative values.

What this type of chart needs?

This graph type works the same as Line chart, it needs at least one series added to Series list. On the other hand, Stacked area chart needs more than one series, as with one series it will be presented as normal Area chart.

How Graph layout works?

Graph layout window allows to choose one of graph styles which differs depending on chart type. There are three ways to access Graph layout settings:

  • by clicking on Graph layout icon

  • by right clicking on the chart and selecting Graph layout option

  • by using the keyboard shortcut CTRL + L


Here, you can define how the graph should be displayed as well as which series corresponds to which axis.  On the right side of the window, the graphed pairs of series are displayed under a graph type. The first series of each pair is placed on the x-axis and the second one on the y-axis. Click and drag to move them if you want to switch the axes.

Example

Graph samples

In this example we presented available types of charts.

Line chart

For elements not mentioned here see Elements of the chart.

Overview

Line chart is the default type of graph which is displayed in all Macrobond documents. It shows data points connected with a continuous line.

It is possible to turn on Markers and set Line as none - this way you can create Scatter chart and Category scatter chart.

What this type of chart needs?

As stated previously, Line chart is the default graph type in Macrobond. It's universal and available in all documents. This type only needs series placed in Series list, after that you can add Time chart where line chart will show automatically. When there's more than one series, it is possible to set one or few of them on Alternate scale.

How Graph layout works?

Graph layout window allows to choose one of graph styles which differs depending on chart type. There are three ways to access Graph layout settings:

  • by clicking on Graph layout icon

  • by right clicking on the chart and selecting Graph layout option

  • by using the keyboard shortcut CTRL + L

Here, you can define how the graph should be displayed as well as which series corresponds to which axis.  On the right side of the window, the graphed pairs of series are displayed under a graph type. The first series of each pair is placed on the x-axis and the second one on the y-axis. Click and drag to move them if you want to switch the axes.

Example

Graph samples

In this example we presented available types of charts.

Column & Stacked column chart

For elements not mentioned here see Elements of the chart.

Overview

Column and Stacked column charts display data as vertical bars. Categories are applied depending on analysis used before. In most cases output can be a time series but after analyses such as Scalar or Slice.

Column

It shows each observation as a single column, when there's more than one series on a graph, it will represent it as separate column in different color.

Stacked column

The difference between Column and Stacked column is that the second one will display all series stacked on top of each other. It's worth to remember that stacked column works only when you have more than one series in your chart, otherwise it will look exactly as normal Column graph with only one series included.

What this type of chart needs?

Column and stacked column chart needs data specified as observations or categories. Both are available in the following chart types:

  • Time chart
  • Category chart
  • Bar chart

How Graph layout works?

Graph layout window allows to choose one of graph styles which differs depending on chart type. There are three ways to access Graph layout settings:

  • by clicking on Graph layout icon

  • by right clicking on the chart and selecting Graph layout option

  • by using the keyboard shortcut CTRL + L


Here, you can define how the graph should be displayed as well as which series corresponds to which axis.  On the right side of the window, the graphed pairs of series are displayed under a graph type. The first series of each pair is placed on the x-axis and the second one on the y-axis. Click and drag to move them if you want to switch the axes.

Example

Graph samples

In this example we presented available types of charts.

Heatmap

Overview

You can create a Heatmap by modifying Bar chart in a way that presents values by assigning them a color from defined range of colors - using Bar chart conditional formatting rules.

How to create a heatmap?

Shortcut from panel

In Actions ribbon you will find a panel with shortcuts which create heatmap with latest 12 observations. Mark all series which you want to use and select one of the options:

Build it

To create a Heatmap first you need to have Bar chart in a form of Dynamic table - this is just a simple table but the values will change after series update. By settings color rules you create Heatmap.

Rules panel is available under Chart properties > Chart elements > Rules. In new window simply press 'Add rule' and start the process:

You can highlight either 'Text' or 'Background' for each cell. For more information about types (Range, Condition, Rank, Average) see Bar chart conditional formatting rules.

Choosing color ranges

Two colors

After adding a rule, select colors for backgrounds and check in 'Include' to which columns should this be applied. Then press OK. See below paragraphs to learn more about the process.

Three colors

Let's say you want to set three colors on your Heatmap, for high, middle and low values. You need to define two ranges of colors.

For example: 0 to 50th percentile as a range from red to yellow and 50th to 100th percentile as a range from yellow to green. This way you will receive a heatmap showing the highest percentile in green, lowest in red and all others in the colors range accordingly to their values.

Applying rules to chart

You may apply a rule to all visible values in the table, or for each column separately.
You may also decide to which columns apply the rule. Note that first column is the the one with descriptions. It's not possible to do apply rules by rows.

Multiple rules - different rules for different columns

In one Bar chart you can have different rules for different columns. Each column is a separate entity, and a rule will apply to each column’s values range separately. For more information about Rules see Bar chart conditional formatting rules.

The Rules will be applied in the order they appear and a Rule further down the list can change settings made by an earlier Rule.

Value series

The default setting in 'Value series' is 'First series'. This means that the range used for coloring cells will be based on that column's value range. You can of course choose another series to be base for a range. This is useful in constructing ranking heatmap - see the file in our Examples.

Using other series for color formatting

You may also use other series to define the range of values of plotted series, even the one not used in the chart.

Example: You have one series with values and another which is condition-formula:

if(fx;s1<0, 0, 1)

And you want to show the actual values but color them using the formula series. '0' value if the change was negative and '1' if it was positive. This would look like this:

Examples

Heatmap

In this example each column is a different indicator. We applied conditional formatting rules based on range of colors to the Bar chart creating a heatmap. Colors are applied 'by column'.

Heatmap - ranking

In this example, we added helper document in-house series to create a heatmap with ranking (by each column). Each country has same color throughout all columns.

Heatmap - USA inflation breakdown

Here we used CPI components and its weights to create a heatmap with values for last year (rolling).

Heatmap - with pictograms

In this example we used main CPI components and added monochromatic pictogram to each category.

Questions

Can I format within a row instead of a column?

No, formatting works only within columns or whole chart.