Dave's Adventures in Business Intelligence

I’ve been inactive on BOB for a while as well as here…recently I was reminded that some of the old questions still come up periodically. For example, how do you reset a running total? It’s not hard, but the syntax is not immediately obvious. For example, examine the data block below. The running total eventually matches the grand total, which is what you would expect.

The formula that generates the running total looks like this:

=RunningSum([Sales])

That’s easy enough. But what if you wanted the running total to reset (start over at $0) for each Region? The answer – as it frequently is for word problems – is embedded within the question. I need to know what measure I am working with (Sales) and which dimension is going to drive the reset (Region). The new formula looks like this:

=RunningSum([Sales];([Region]))

And here is the output.

But how do you know this? When I’m working with a function I haven’t touched in a while (or never) I always look at the help text at the bottom of the screen where I am building variables. See here:

The syntax shown on the help text is this:

num RunningSum(measure[;Row|Col][;reset_dims])

Breaking that down, I know:

• The output value from this function is a number
• The name of the function is RunningSum()
• The input value (singular in this case) should be a measure

Anything in square brackets is considered optional. In this case:

• ; is probably a delimiter of some kind
• Row|Col is read as “row or column” because the pipe | is usually an Or operation
• I can also reset based on a list of dimensions

So from all of that (and perhaps some trial and error if I haven’t used this function before) I should be able to interpret the “reset_dims” as a list of dimensions to reset my running total, and that would be correct. 😎

Each of the “running” functions uses the same syntax. The only difference that I noticed is that the help text for RunningMin() and RunningMax() looks like this:

input_type RunningMin(measure[;Row|Col][;reset_dims])

The difference is subtle…the input value for these two functions is not restricted. I can take a minimum of a character field or a date field in addition to a numeric field.

So there you have it…an immediate answer to the question, “How do I reset a running total?” as well as a tip on how to learn how to learn and then use new functions you may not have seen before, or to extend formulas with functionality you were not even aware of.