How can I create a subtotal for different time periods from different versions in a matrix report. Currently, there is 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 is not a formulaic way to do this calculation.Can this subtotal be accomplished another way?
Yes, this can be accomplished by using using the "Display as: Custom" function in reports.
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. In 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.
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. Lastly, 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.
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.