If you’ve ever wanted to calculate the sum of the previous periods in your calculations, but found when you applied the range formula it just didn’t work out right, here’s a reason to why it may not have worked.
Check Time Parameter in Formula
Firstly, when calculating a sum of the previous ranges in a time-based sheet (such as calculated accounts in a Modelled Sheet, or any row in a Standard Sheet, or an Assumption row, or any row in a Cube Sheet – where the horizontal axis is Time) your formula should contain the
time parameter followed by a range of time elements all separated by a colon.
The above formula simply calculates the number of accumulated weeks in a month from the previous month to eleven months ago.
Your range formula therefore needs to be of the pattern:
time– the parameter denoting the capture of a time range.
this– in reference to what time-period to capture (by default if your sheet is set to
monththen it is fetching the time period in months). You can fetch quarters or years too using
-X– where X is the offset number of periods to start FROM (must be the earliest date value).
:– (optional) using this means you’ll be using a range of time periods to capture.
this-X– as above, references the current sheets period, otherwise you can use
this.yearto fetch other time periods.
-X– where X is the offset number of time periods to calculate TO (must the latest date value).
Check Account Settings
So assuming we are using the correct pattern for writing our sum range formula the only element remaining is how we’ve created our account. This means if our pattern doesn’t work as expected it’s likely our settings on the account you’re referencing is not correct.
As an example, for a Cube Sheet where we’ve created a Calculation account we need to ensure that the Time rollup field is set to Sum of rolled-up values and not “Average of rolled-up values” or “Weighted average of rolled-up values”. Having it set to the other two will calculate the average being applied to your range, but will not give you the correct value if you’re looking to obtain the sum.
If you need to keep the setting of the account to an average, but would like to be able to still calculate the sum of a time-period range I would look at using a rudimentary form such as:
ASSUM.WksMonth[time=this-1] + ASSUM.WksMonth[time=this-2] + ... ASSUM.WksMonth[time=this-11]
It would really depend on what the focus of your account is doing and for establishing the correct settings when developing these accounts.
Check Sheet Settings
One other check you could perform is to make sure you have the correct understanding of the time period references in your sheet.
If you conduct budgeting fairly infrequently you may forget the time structure of a sheet and when you create your formulas you might think the sheet’s settings are of a particular time period, when in fact they are not.
This happened with one particular modelled sheet I was operating on where it was set to
daily for the time periods. This would mean a formula like
ROW.SomeAccount[time=this-11:this-1] would only be referencing the previous 11 days, not months for which I was intending.
To check the time stratum of your modelled sheet click on the Columns and Levels link, then on the Settings icon, then Settings, you should see a page like so:
If you’re struggling to fetch the right values in your formula, and the returned values just aren’t working out right, check the following:
- The formula range is referencing the correct range you’re seeking;
- The account settings are correct, including any references you may have to other ROWS; and
- The sheet’s settings are as you’ve anticipated in designing your formula.