AdHoc Subsets as an alternate to Select function

[PrevContributor]

Hi, From time to time, it may be convenient to create an adhoc subset as an alternative for the SELECT Function. Business case :

• Source module : full list
• Target Module : need to select only one line item (or more than one) from the source module.

Proposals :

• Option One:: Formula in the target module will look as follow : Source.line item[SELECT: Full List.Relevant item]
• Option Two : use the subset in the target module. Formula will be easier : Source.line item.

The subset will do the filtering job (selectng) by himself. This works all the better if you need to pick only some but multiple items from Source Full List  I call the option two "Select Alternative for Lazy Peoples". Kind regards. Michel

[PrevContributor]

Yes, using SELECT made me die a little inside too so I designed this method instead. It’s pretty simple, the space saving is not as obvious in the following example but, if you find yourself having to sum down a dimension that is deep within a hierarchy and before you are forced to use Summary Types then this is much better. Define Lists: di01, di02, di03 Arbitrary length and element names. The lists need not have a top level item. In my example the lengths of di01, di02 & di03 are 20, 100 & 50 respectively. Define List: OneState One element, namely, 'State'. This list also need not have a top level item. Define Module: Box Applies to: di01, di02, di03 Define Line Item: Value Data Type: Number Fill this Box module with random data. Cell Count: 100,000 Define Module: Lists to OneState Applies to: N/A Define Line Item: di02 Applies to: di02 Data Type: OneState Formula: OneState.State Define Line Item: di01 & di02 Applies to: di01, di02 Data Type: OneState Formula: OneState.State Cell Count: 2,100 Define Module: Collapse di02 Applies To: di01, di03, OneState Define Line Item: Sum Date Type: Number Formula: Box.Value[SUM: Lists to OneState.'di02'] Define Line Item: Average Date Type: Number Formula: Box.Value[Average: Lists to OneState.'di02'] Define Line Item: Max Date Type: Number Formula: Box.Value[Max: Lists to OneState.'di02'] Define Line Item: Min Date Type: Number Formula: Box.Value[Min: Lists to OneState.'di02'] Cell Count: 4,000 Define Module: Collapse di01 & di02 Applies To: di03, OneState Define Line Item: Sum Date Type: Number Formula: Box.Value[SUM: Lists to OneState.'di01 & di02'] Define Line Item: Average Date Type: Number Formula: Box.Value[AVERAGE: Lists to OneState.'di01 & di02'] Define Line Item: Max Date Type: Number Formula: Box.Value[MAX: Lists to OneState.'di01 & di02'] Define Line Item: Min Date Type: Number Formula: Box.Value[MIN: Lists to OneState.'di01 & di02'] Cell Count: 200   Using the OneState Dimension as a root of all your data aggregation and calculation modules lends you a huge amount of flexibility. As you can see you can extract SUM, AVERAGE, MIN and MAX using this method without any Summation Types or the SELECT function. The source module may or may not have the OneState dimension applied to it so this method can be chained (only relevent for the non-commutative operations of course). This can be used in many areas of aggregation but also conditional formatting. Using a 3 colour scale you can construct a piecewise affine mapping of the Min to 0, the Average to 0.5 and the Max to 1.

[PrevContributor]

I might also add that this is a general definition of operations down an entire list. To perform operations down subsets then the Lists to OneState module would instead want a set of Line Items that are either user input or conditionally places OneState.State in the mapping. This definitioin should serve as a basis for the solution to a number of problems.