Worksheet – Case #7 – Disaggregating a series – an example with Chinese series

Finding Data

For this case, you will be using a Retail Trade indicator for China. So, best is to start in the Country & Region category.

Go to:

Country & Region > China > Trade > Domestic Trade > Retail Trade > Totals. 

Locate and add:

  • China, Domestic Trade, Retail Trade, Consumer Goods, Total, Current Prices, Aggregate, CNY

Data Preparation

Replacing missing observations

First, let’s add a time table:

  • Right-Click on Series List
  • Select “Add” and choose Time Table

On the time table, you can notice that January is missing for every calendar year. We can enable that month to appear by going through a few steps:

  • First, go to "Series list" in the tree
  • Click on the “Observations” drop-down menu
  • Select All points

This setting will force the application to show all points in time (i.e. dates) for the frequency chosen, here monthly.

So, returning to the time table, you will notice that January has been added as an observation, containing the value from its preceding month, which is the default setting.

For this example, however, January should take the value from February each year.

  • Return to "Series list" in the tree
  • Click on the "Conversion settings" tab
  • In the drop-down menu "Missing value method", select Next value


Adding a formula analysis

To disaggregate the time series, you will have to access the formula language function. While we could have written it as a new expression in Series List, using the formula analysis will be easier:

  • Select “Time chart” in the tree
  • Click on “Insert” and choose Formula
  • Click on the small button labeled fx

Working with the formula language

The data is aggregated on a yearly basis, from January to December.

To disaggregate it, you need to subtract the current value with its previous value, except in January and February.

There is therefore a condition needed this calculation: being after February. In Macrobond, you will need to use the following formula: if(condition, value 1, value2)

The condition is to be between March and December. In other words, after February. In Macrobond, you can write such condition with: month()>2.

Value 1 is what you want to do when the condition is true. In our case, we want to disaggregate the data. Calculating the difference between a value and a previous value of a series can be achieved with the formula: momentum(series, length), where length is the observation window between the value and the previous value. Here, you want to subtract the value of the previous month, so the length is 1.

Value 1 will therefore be: momentum(fx:s1, 1)

Value2 is what you want to do when the condition is false. In this case, what we wanted is to distribute the February value between January and February.

Value 2 will therefore be: fx:s1 / 2

So you now have the entire expression, which should be:

If(month()>2, momentum(fx:s1, 1), fx:s1/2)


  • Uncheck the tick-box "Include input series as output"
  • Click on "Done" at the bottom right corner

If you click on "Time chart" in the tree you will notice the line fluctuating less between different observations than before.

Other changes

  • Title: Disaggregating a monthly series
  • The analysis text has been removed from the legend