AdHoc Subsets as an alternate to Select function

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

Tagged:

Answers

  • 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.
  • 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.