Skip to main content
Adaptive Insights
Knowledge and Support - Adaptive Insights

How To Subtotal Different Time Periods From Different Versions On a Matrix Report

Goal

In Adaptive Planning, there is currently not a native way to subtotal values from different time periods in different versions. For example, if you want to subtotal first six months of a given year of the Budget version and then add them to the last six months of a Forecast version, there would not be a formulaic way to do this type of calculation. However, you can do this by using a matrix report and using the "Display as: Custom" function

Assuming that there are already 2 segments set up, one with one plan version and first 6 months and the second segment contains a different version with the last 6 months, then we can set up a third segment that will contain our calculations. In this exercise, we will use a "Display as: Custom" as well as a difference element. DisplayAsCustom1.pngIn the difference element, please include the two versions that you wish to sum together and make sure to offset the "Subtract Version" to 6 months Backwards.DisplayAsCustom2.png

Then, place a custom calculation under the difference option that sums together the last 6 months, however, because the difference element is offset, it will sum the first 6 months with the last 6 months. DisplayAsCustom3.pngLastly, you will need to include the "Display as: Custom" element on the third segment. And in this element, you can include a formula as well and the formula will read: if(this.version.name = "default version", -ACCT.this, ACCT.this) where the "default version" is the version we offset earlier.DisplayAsCustom4.png

We basically are tricking the difference element into being a sum. The reason we use the difference is because it allows us to operate with 2 different versions in a single calculation, where as it is impossible, currently, to formulaically refer to values in another version from you current version.

What's Next

This is what was achieved and what was omitted in this how-to.