Our video of the day looks at the new hierarchical support for context calculations.

Context functions let users build calculations that can only be resolved efficiently in the context of a report. This includes calculations like the difference between values, cumulative totals, rolling averages, and more.  

If the query contains a hierarchy, you’ll need to choose the method used to apply the calculation to the hierarchy levels. This is done in the context calculation editor, which presents hierarchy options. These options let you choose the method used to apply the calculation to the hierarchy levels. You can constrain the context calculation by each elements’ hierarchy level, or by each elements’ parent level.

In this example, we have a grid showing Cost and Sales by Date hierarchy. We have a third column, Cumulative Sales, generated using the cumulative values context calculation. In the context settings, the hierarchies are set to “none” (red highlight below), telling Pyramid to ignore the hierarchical structure in the query.

The problem with this is that the cumulative values are accumulating by row, as we see when comparing the Sales and Cumulative Sales columns. When we expand each hierarchy, we expect to see the values pertaining to that level. Instead, when we open the Q1 2008 level, we see the total for 2008 is accumulated with the total for Q1 2008, which is then accumulated with the total for Jan 2008, and so on. As such, the values in the Cumulative Sales column are incorrect, and change depending on which hierarchy levels are expanded.

 Here, the hierarchies have been constrained by level, so the values of the context calculation accumulate in each row of the hierarchy level, flowing from each level to the next. The total for each parent row is equal to the value of the last row of its child level; 2008 has the same value as Q4 2008, and Q2 2008 has the same value as Jun 2008.

 And here, the hierarchies have been constrained by the parent, so the cumulative sales accumulate in each row that shares the same parent level. So only the direct children of each level accumulate to the value of their shared parent. Thus, Q1 2008 is equal to Mar 2008, but Q2 2008 is not equal to Jun 2008.

In the next example, we have an accounts grid showing the amount for Actuals and Budget per account. Using a context calculation, I’ve added two Percent of Total Amount columns, and I’ve set the hierarchy type to Parent. Each row of the context calculation columns shows the percentage that the corresponding hierarchy level accounts for in its direct parent level.

For instance, the top level includes “Property, Plant & Equipment, Net” and “Operating Expense”; the former accounts for 9.59% of Actuals total amount, and the latter accounts for 90.41%. 

Expanding the hierarchies, we see “Property, Plant & Equipment, Gross” makes up 100% of its parent, “Property, Plant & Equipment, Net”. Expanding further, Property accounts for 17.16% of its parent, “Property, Plant & Equipment, Gross”, Computer Equipment accounts for 68.72%, and Broadcasting Equipment makes up 14.12%.

 And expanding Computer Equipment, we see it’s made up of Desktops/Peripherals (11%), Infrastructure/ Servers (25.84%), Computer Software (63.16%), and Website Development Costs, which accounts for nil.

How to set the Context Calculations for Hierarchies

In this video, we will show you how to set the context calculation behavior for hierarchies.  For more details, see our help pages.

Let us know how you like these small tips below. Have a look at our Use Case Course on Parent Child Hierarchies in the Learning Hub. (Note: The link points to the Version for Business users, there is also a more technical one for Modelers)