Step 1: Add growth formula

The first step is to insert a new growth formula into the pivot. In our case, we wish to add growth for both Sales and Cost.

To do that:

1. Use the mouse to select the Sales & Cost measures inside the pivot.

2. Right click over the newly selected cells, and from the popup menu, select the New Quick Function menu item.

3. Go into the Growth sub menu, and select the Growth Over Previous Period menu item.

The resulting pivot has two new columns, each representing a growth formula for the preceding measure (Sales & Cost respectively)

Note:

April has growth of 0% since it is the first month in our data source.

Step 2: Format growth as percentage

Our Growth formula is measured in percentage, therefore we shall use Prism's context based formatting options to quickly adjust the growth cells to display percentages instead of scalars.

To do that:

Select each of the Growth formulas in the pivot, and apply the percentage (%) numeric mask on each one separately.

Before:

After

Step 3: Modify growth formula

Right now, each growth formula compares the appropriate measure against the previous time period. June is compared with May, and May is compared with April.

But suppose we wish to compare each time period with the one that occurred two time periods ago.

To do that:

1.Select the second growth formula: Growth(Cost).

Once we click on a formula, Prism automatically displays the formula's syntax inside the formula editor.

2.Move mouse over the formula editor and click on it. item.

Note:

The formula editor turns yellow if it is currently in edit mode.

3. Modify formula to go back two periods

Tip:

You can always define a growth formula from scratch by entering your own syntax and using the Prev() formula just like it is used by the quick function.

Cost diff / Cost prev : Cost diff = Cost - Cost prev Cost prev = Cost in previous period

4. Click [ENTER] or press on the Apply button.

We can see now that the Growth(Cost) formula performs a comparison over two periods. We can also notice that both the April and May members return zero values because they have no two older periods to compare with.

Tip:

If you wish to apply N periods on a quick time function without manual modifications to the formula, use the Count Back Periods drop down menu item inside the popup menu.

Step 4: Add formula into repository for future usage

We have successfully added a growth formula into our pivot widget. Now we want to "store" it for future usage in other widgets.

To do that:

1. Right click over the growth(Sales) formula inside the Pivot widget.

2. Select the Add Formula Into Repository popup menu item.

3. Enter a unique name for the growth(Sales) formula and click on the OK button.

The Data Browser now contains the new growth formula and we can use it in other widgets and other formulas, just like a regular measure.