Reference another subset within aggregate function
Hello, I have what might seem to be a rather simple requirement, but being new to QV, have yet to find any good examples or clear documentation suggesting that QV can in fact do this:
Given the following set (13 months in descending order from May 2014 to May 2013)
Based on what I've read, to get the OrderQuantityLastYear, column, one would enter a set analysis expression like this:
Sum ({<Year={$(=Year-1)}, Month={$(=Month)}, ...>} OrderQuantity)
or even like this:
Sum ({<Year={2013}, Month={5}, ...>} OrderQuantity)
Where ... are the remaining columns that should be equal to the current record. But this does not have the effect of summarizing records with year 2013, month 5, and appending the result to the current record in a new column -- the output is always just 0. Am I doing something wrong here, or is this just not possible? Is seems so simple...
So two requirements of this type of report would seem to be:
1. The ability to perform mathematical operations within a set expression. As none of the examples in the documentation show this, it may simply be unsupported.
2. The ability to reference the "current" record on the right-hand-side of an expression. The examples in the documentation that reference a dimension on the RHS, are referencing the entire set of values for that dimension, not the "current" value.
As it seems these things are not supported, I've moved the work done here to the database engine, however that has the undesirable qualities of being both inflexible (the user cannot easily change the report) and self-joins required to implement this in SQL can be taxing on the query engine. Any gurus out there able to show that these two capabilities are in fact supported in QV?
