This feature is available to Knoema Professional subscribers.
When you create visualizations in the dashboard builder you may need to apply some calculations to the raw data. These may include percent change, moving average, percentage share and so on. To do this, you can use the calculations editor which is available in the customize mode of the gadget editor.
To add a calculation, do the following:
1. Open any dimension which you want to apply a calculation to and click “Add calculation”.
2. Click an empty placeholder and choose a dimension element you want to put in this placeholder.
3. Click a member of the formula and select a calculation that you want to apply to this member.
You can select simple arithmetical actions like plus, minus, multiply, divide, and brackets or functions available in the drop-down menu. For example, you can use different growth rates:
- % Change - change from the previous period
- % Change, YoY - the year-over-year growth rate shows the percentage change from the past 12 months
- % Сhange, YTD - year to date, stands for percent change from the beginning of the year
- % Change, annualized - change from the previous month raised by 12 in the case of monthly data, and by 4 in the case of quarterly data
- % Change, previous value - percent change from previous value (useful for data when it includes null points for time period)
- % Change from year ago - the change from the same month of the previous year
- Change - the absolute change from the previous month which represents value in the current month minus the value in the previous month.
- Change, YTD - year to date, stands for absolute change from the beginning of the year
- Change, QTD - year to date, stands for absolute change from the beginning of the current quarter
- Log Diff - the difference of natural logarithms of the current and previous period which is equivalent to the % change.
Different types of transformations are available as well:
- Log DIff, YoY - the decimal log of the difference between two values within year on year changes
- Log Diff, YTD - the decimal log of the difference between two values stands for absolute change from the beginning of the year
- Log Diff, QTD - the decimal log of the difference between two values stands for absolute change from the beginning of the current quarter
- Log Diff, previous value - the decimal log of the difference between current and previous value
- Moving Average - moving average (trailing) across x points
- Moving Average, YoY - moving average (trailing) across x points within year on year changes
- Moving Sum - moving sum (trailing) across x points
- Moving Sum, YoY - moving sum (trailing) across x points within year on year changes
- Sum, YTD - sum of the values year-to-date
- Sum, QTD - sum of the values quarter-to-date
- Sum, YTD, YoY - sum of the values from the beginning of the year within year on year changes
- Sum, QTD, YoY - sum of the values from the beginning of the current quarter within year on year changes
- ABS or Absolute value - is the function that returns the absolute value of a number.
There are also several special functions available.
- Ratio To is the ratio of the selected item to some item from another dimension available in a dataset.
- Sum allows to sum several elements with simple selection one by one.
- t-1 and t+1 return values from the previous or the next period. Multiple clicks on these functions allow you to increase the lag.
- Const - if you need to add a constant value for the calculation
- IfNull converts empty values to some constant values, like zero. This is convenient when you have missing values.
- [X] function is for the creation of batch formula meaning that you can create a formula where instead of [X] you can select the elements to be put to your formula instead of [X].
4. Change the name of the formula and click the “Save” button. You will see a small green F indicating that an item contains calculation.
You also have the options to add calculations to the time dimension. This is available when you select “Individual members” representation of the time members.
The formula editor for time dimension has the same simple arithmetical actions and constant. It also has IfNull and batch formula. But instead of the drop down list of standard functions it has functions of arithmetic or geometric average, maximum and minimum, and the sum. All of these functions work similarly: you select start and end dates and get average, sum, min or max within that time frame.
There are several nuances of visualizations with calculations.
- Once you add a calculation to your gadget, you are not able to change visualization types, for example, from chart to map.
- You also cannot open a visualization with calculations in the dataset viewer.
- Automatic units will be reset after applying a custom calculation (except for % change). So, you may need to manually add a new unit in the appearance tab.
- We do not recommend adding calculations to several dimensions simultaneously.