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?
Answers
-
I don't believe the SUM function has this capability natively.
As a workaround is it possible to create stage line item(s) in the input data module that flips the sign or modifies the value with a conditional statement based on a lookup of the group before summing into the output module? Or are the lists large enough that this would be a size concern?
0 -
your OUT cannot access the DATA module by groups without crossing items and groups.
What about the sparsity ? Are most items added/subtracted into all groups ? Or is it sparse ? If sparse you might win a lot of space by creating an "operation" child list (with the group as prop) of item for example, and you could sum on that (or the contrary, items as child of group)
0
