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.