SUM on a list attribute that varies with another list?
Is there a clever way to SUM on an item's attribute when that attribute varies depending on another list?
I have two very large flat lists ... Items and Groups .. and another small picklist, Operation, with three members Add, Subtract, and Ignore.
Data module "DAT 1 - Input Data" is dimensioned by Item and Time (Month). There is one line item: Value (number format).
Module "SYS 1 - Mapping" is dimensioned by two subsets: SS Selected Groups and SS Included Items. These are the Groups the user selects and the Items that are in any of those selected group. Remember the Items and Groups lists are huge, so we cannot dimension by the full lists. I'd be perfectly happy to use small lists instead of subsets if that is better. There's one line item Operation (format: list Operation) that tells whether the data line is included in the group by addition, subtraction, or ignored. (I can calculate the subsets and this attribute from other info in the model, just treat them as manual inputs for now to keep things simple.)
Module "OUT 1 - Output" is dimensioned by SS Selected Groups and Month. The module has one line item:
Net Value = sum 'DAT 1 - Data'.Value for Lines with Add attribute - sum'DAT 1 - Data'.Value for Lines with Subtract attribute.
Again, because there are millions of Items, we don't want to dimension output by Item x Group ... too many cells. Fortunately, I never need to show Item level of detail. I should be able to simply SUM on the item's Operation (Add or Subtract) attribute.
I am struggling because the Add / Subtract / Ignore attribute of a data line varies by Group. A line may Add to Group 1, subtract from Group 2, and be ignored in Group 3.
Is there a clever way to SUM on an Item's attribute (Operation) that varies depending on the Group context?