I would appreciate any advice on the best way to specify expressions that apply to groups of rows based on data values.
Seq Type Desc Code Balance
20 ASSETS Cash and Equivalents 1100 10,000
25 ASSETS Receivable Net of All 1210 10,000
30 ASSETS Inventory 1410 10,000
40 < ARBITRARY EXPRESSION HERE >
50 LIAB. Payroll 2410 10,000
60 < ARBITRARY EXPRESSION HERE >
Across the dimensions of Seq, Type, Desc and Code it is obviously possible to specify normal chart sub-totalling.
Instead, I need to "plug-in" a number of additional chart rows at say lines 40 and 60. These need to contain customizable expression code. So maybe Seq 40 might be the balances of Seq ( 20 + 25 ) - 30. In other words it is not a straightforward Sum. The Seq of 20,25 and 30 do not belong to a parent dimension.
The expression needs to manipulate the values held in other rows. Seq 60 might be the balances of Seq 40 ( itself derived ) - 50.
The data table can contain the rows for 40 and 60, but it is the syntax or methodology for basing the expression on the other rows of the table that I am struggling with. Rows 40 and 60 have no balances of their own. I cannot calculate the balances in the source database, or the script as the values need to change based upon user selections.
I am using QV11SR1. I have tried using Set Analysis to derive these expressions but with no luck. I am not familiar with the AGGR function.
Any advice would be much appreciated.