Running totals/averages

4.62K viewsFormulas and Functions
0

I have several years of data for which I would like to have 6 and 12 month running averages. Is there anyway to do this and still maintain a separate “year” category? It seems once I create a separate category for the year, I lose my ability to use a single formula for calculating a running average.

I can “group” the years, and then use a single formula, but this then necessitates additonal unique formulas for other data that is “yearly” based.

Appreciate any help. Love the program.

0

Stefan, here is a sample model that addresses looking 12 months ahead using a year and month category. Of course, I can’t take credit for the SELECTBETWEEN approach :) but is a very eloquent way to solve the problem. The basic idea is to create a sequential month key on which the SELECTBETWEEN function can use to find the next twelve months.

I have also provided a different approach in which I made a separate time dimension just for the debt. This allows you to start each debt instrument at month one and march forward. This will allow the model to easily be expanded to include an amortization schedule for each piece of debt. To bring the values of the current portion onto a balance sheet, you use the USING/AS function to get the appropriate year/month combination.

0

Hi Mike,

Did nested categories get implemented?

I have a requirement to determine the short term and long term portions of loans outstanding. The short term portion in any given month is the sum of the principal payments due in the next 12 months.

I have years categories and months. I can use [NEXT+1], [NEXT+2], etc., but it seems I have to build 12 different formulas because they don’t span the year category.

Is there any better way to do this?

– Stefan

0

The development team is working hard on the next major release tentatively scheduled for early 2005. One of the big features of this release will be data integration functionality with existing data sources.

-Mike

0

Thanks Mike. Out of curiosity – when do you expect the next major release of the product?

0

Hi Tom – Thanks for the post:

Attached is a model file that demonstrates doing a rolling average between years. In this case… the prior 3 periods.

With 1.2, as you stated you have to do this with multiple formulas. With our next major release, we plan to introduce a concept of ‘nested categories’. This would allow you to establish a relationship between the Years category and the Month category which will allow you to use only one formula in the rolling average calculation.

I hope this helps and I will keep the forum community posted on our ‘nested categories’ functionality.

-Mike