Running Totals on Slice and Dice Dialog

In this article you will learn how to add a running total of a metric to a report without writing a metric.

The example shows how to create a running total table calculation on the Slice&Dice dialog (also known as 3CD) using the GoodSales demo project.

How it works

Imagine a situation when you want to see the progress of all deals, regardless of stage or status, sliced by Month and Quarter so you can compare the growth between the quarters.

You probably start with a simple Sum of all deals. In the GoodSales demo, this metric already exists so you can choose

What:

SELECT SUM(Amount) where Opp. Snapshot (Date)=_Snapshot [EOP]

How:

Month of Quarter (Closed), Quarter/Year (Closed)

As the How parameter, choose the the Month of Quarter (Closed), Quarter/Year (Closed) option, to be able to see progress Month-over-Month in quarters. Because you are only interested in the year 2011, apply the attribute filter with quarters from 2011. After you click on the “Apply” button you should see this result:

Now, assume that you want to add a running total (a running sum). To add a running total table calculation we right click on our Amount metric and we navigate ourselves to the Running (sum, avg, …) section of Totals as on the picture below.

 

Choose the Running Sum and you should see the Running Sum of the whole year 2011:

Typically, however, you need the running totals for the time-over-time (eg. quarter-over-quarter) growth comparison. To get that comparison, simply add any of the totals using the user interface, except the running one, for the granularity on which you want to see the comparison.

In the example, start with the quarter-over-quarter growth comparison - add a subtotal for the Quarter/Year (Closed) time attribute.

 

As a result, you will get the running sums of your deals for each Quarter.

If you want to show the deals and the running totals side by side for different products, simply add the Product to the How:

There are many types of table calculations including:

  • Sum
  • Average
  • Minimum
  • Maximum
  • Rollup
  • Median
  • Running totals

All of these types of calculations can be added by secondary clicking the metric name as explained in this example.

Running Total Statistical Functions