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 is a change from the previous month
- % Change, annualized is change from the previous month raised by 12 in the case of monthly data, and by 4 in the case of quarterly data.
- % Change from year ago is the change from the same month of the previous year
- Change is 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
- Log difference is the difference of natural logarithms of the current and previous period which is equivalent to the % change.
You may also select one of the standard functions:
- Moving Average is the average of several preceding periods which is used to smooth out seasonal fluctuations.
- YTD (year to date) is the sum of values since the start of the year.
- ABS 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.
- 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.
- 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.
- You cannot create calculations using data from two different datasets.
- We do not recommend adding calculations to several dimensions simultaneously.