Comparing data with Calculated Items

  • 21 December 2021
  • 0 replies
  • 1556 views

Userlevel 5
Badge +3

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 and some examples. 

 

 

When deciding to add a Calculated Item over writing a Formula in a Metric, it is important to understand that Calculated Items can not be referenced by other Metrics. 

 

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 the Calculated item or the name of your preset.
  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, and your new item is ready!

 

Calculated Items on stacked or group dimensions

 

You can add calculated items on stacked or grouping dimensions within a Metric or Table.  Stacked dimensions are when you place a dimension on top of another dimension within the pivot. Working with stacked dimensions operates similarly to when working with an item at the lowest position in the pivot. The calculated item will appear at the same level in the pivot as the item that it is applied to.  All dimensions positioned below it will appear in the same order.

 

A grouping dimension is when you use a grouping property to display data at a different level.  For example, If your data is at the Month level, you might use a grouping property to display it by Quarter or by Year.  When added to a pivot, these are easily identified by the original dimension a  > symbol then the grouping dimension.  For example, Month > Quarter. 

When you add a calculated item on a grouping dimension, it will automatically turn on subtotals that will be set by default to SUM.  This is how the calculated item performs its calculations.  If you adjust the subtotals, the calculation will also adjust.

 

Look at the example below. The subtotals are highlighted below in blue, calculated item in orange, and the grouping dimension and calculated item in green. In order to view Quarter over Quarter growth between Q1 and Q2, subtotals set to SUM were created for the three months making up the quarter.

 

 

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.
Sum: A+ B Calculate the total of 2 specific items of your dimension through addition.
% 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 on 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 and the time dimensions in your pivot, so 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. 

 

Calculated Item, Show Value As or Formula?

Year to Date can be calculated in different ways in Pigment.

When using Calculated Items presets, such as Year to Date, its important to note that these calculations can not be referenced by other Metrics. To reference the results, use the YEARTODATE function. If you want to get a quick Year to Date for an entire Metric use the Year to date Show Value as preset.  

 

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

 

 


This topic has been closed for comments