Untitled Page

SiSense Learning Center

Tutorial 5: Working with quick time functions Start Video

Introduction

Quick time functions allow you to easily add time related formulas such as growth,
difference, contribution, running sum/avg, rolling sum/avg etc. into your widget.
Here is a list of currently available quick time functions:

Growth
Difference
Contribution
Running Sum/Average
Rolling Sum/Average

The following tutorial will teach you how to work with quick time functions inside a Pivot Widget
and how to modify them for your specific needs.

Before you start...

Open the prism document that represents the previous tutorial : Tutorial 4.psm

Jump To:

Step 1: Add growth formula
Step 2: Format growth as percentage
Step 3: Modify growth formula
Step 4: Add formula into repository for future usage

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)

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.

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

3. Modify formula to go back two periods

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.

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.