Comparing data with Calculated Items

  • 21 December 2021
  • 0 replies
  • 262 views

Userlevel 3
Badge

In Tables or Metrics, using a Calculated Item allows you to add a calculation using data from specific items within a Dimension. If you want to compare data from all items within a metric, check out this article on Show Value as.  This article will show you how to set up a Calculated item, as well as some examples. 

 

Adding a Calculated Item

  1. Click in the arrow next to one item of your Dimension and select Add calculated item or click on the arrow next to a Dimension and select Add calculated item
  2. Give a name to your new Item. By default, the name is Calculated item
  3. Select the position of your item: Before or After a specific item or all items
  4. Select the type of calculation you want
  5. Select the specific items you want to use for your calculation
  6. Click on Apply, your new item is ready!

 

Types of Calculations 

When setting up a calculated item, you have different calculation options that you can select. 

Calculation Type Effect 
Difference: A - B Calculate the difference between 2 specific items of your dimension through subtraction.
% Growth: (A-B) / B Useful for growth calculations between 2 specific items of your dimension
% Ratio: A / B Calculate the ratio or Percentage of between 2 specific items on your dimension.
Offset

Offset will pull a value from another item in the dimension.  If you choose Next offset will pull values from the future or right of a dimension.  If you choose Previous will pull from the past or left.  For Example, if you are using a monthly calendar, a Previous Offset by 12 will give you a value from that month last year. 

Cumulate  Cumulate allows you to keep a running total where items are added up sequentially.  When using Cumulate, you can reset values based upon List properties with a type of Dimension.  Cumulate is not affected by Filters or Page view selections. For example, When cumulating on March it always includes, January, February, and March values, even if they are not in view or are filtered. 

 

Calculated item Options

Here are some of the options you see when adding a Calculated Item.

Position - This will determine where the item appears in a table or metric.  By default, it will go after the selected cell.  You can change the cell and whether it should be before or after that cell. 

Item - When choosing which items to compare, they are referred to as Item A and Item B, this is to help illustrate where each item will be located within the calculation.   For example, there is a calculation type of Difference: A - B, in this calculation the item specified in Item A is subtracted by Item B. 

Offset - This allows you to take the value of an item from either the past (Previous) or future ( Next) how far is determined by the number specified in the By field.  You must also specify which dimension to offset the item by.  

Cumulate - when toggled on adds up the values to give a running total based upon the dimension it is applied to.  This dimension must be present in either a row or column. The cumulate will be applied across all items within that dimension, even if they are filtered or visible. 

Direction - When choosing cumulate, you can choose to cumulate previous items or next  items.  Previous will cumulate items from the past up until the Item identified at the top.  When using previous you can use the Include current item to add the value of the item identified.   Next will cumulate the values of all future or next items within the identified dimension. 

 

Editing a Calculation for a Specific Metric

If you are working on a Table, you can modify the calculation of a Calculated Item for a specific Metric. To do so, right-click on the cell and select Edit Calculation for this Metric. You are now able to modify the type of calculation. 

 

Presets

 

Pigment has put in place some Calculated Item presets for the most used computations. The presets you will be able to load depend on the granularity of your calendar as well as the time dimensions in your pivot, so that you cannot choose an option that can’t be computed. They will be shown in the Show Value as menu, below the various methods of computation, but you will also be able to load them directly from the configuration modale by clicking on “Select Presets”.

The list of presets available is constantly evolving and include notably Last Year, Year over Year in value, Year over Year in %, Year to Date, Year to Go, Month over Month, Last Month, Quarter over Quarter, Last Quarter, Week over Week, Last Week. 

 

Examples 

 

Check out the examples below, you’ll notice a few of them are combining offset and cumulate to other calculation types. 

 

Configuration 

 

Explanation 

 

You can calculate Last year’s value by offsetting 12 months or compare Actuals vs Last year by combining an offset with another calculation type. 

    Year to date 

 

Year to date calculations can now be done by selecting the Cumulate calculation and applying it to your calendar dimension.  

Last year’s year to date

 

By combining Cumulate and Offset, you can calculate last year’s year to date.  

Year over Year growth 

 

Here is an example showcasing how to calculate year over year growth.  You can see that you are able to use the same Item  A and B.  The difference is that  Item B is offset by 12.  

You’ll also notice that Cumulate Value is turned on, meaning both items will have cumulated values

 

 


0 replies

Be the first to reply!

Reply