topic RE: AdHoc Subsets as an alternate to Select function in Anaplan Platform
https://community.anaplan.com/t5/Anaplan-Platform/AdHoc-Subsets-as-an-alternate-to-Select-function/m-p/1576#M2529
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.
<B><U>Define Lists: di01, di02, di03</U></B>
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.
<B><U>Define List: OneState</U></B>
One element, namely, 'State'. This list also need not have a top level item.
<B><U>Define Module: Box</U></B>
<B>Applies to: di01, di02, di03</B>
<B>Define Line Item: Value</B>
Data Type: Number
Fill this Box module with random data.
<B>Cell Count: 100,000</B>
<B><U>Define Module: Lists to OneState</U></B>
<B><B>Applies to: N/A</B></B>
<B>Define Line Item: di02</B>
Applies to: di02
Data Type: OneState
Formula: OneState.State
<B>Define Line Item: di01 & di02</B>
Applies to: di01, di02
Data Type: OneState
Formula: OneState.State
<B>Cell Count: 2,100</B>
<B><U>Define Module: Collapse di02</U></B>
<B><B>Applies To: di01, di03, OneState</B>
<B>Define Line Item: Sum</B></B>
Date Type: Number
Formula: Box.Value[SUM: Lists to OneState.'di02']
<B>Define Line Item: Average</B>
Date Type: Number
Formula: Box.Value[Average: Lists to OneState.'di02']
<B>Define Line Item: Max</B>
Date Type: Number
Formula: Box.Value[Max: Lists to OneState.'di02']
<B>Define Line Item: Min</B>
Date Type: Number
Formula: Box.Value[Min: Lists to OneState.'di02']
<B>Cell Count: 4,000</B>
<B><B><U>Define Module: Collapse di01 & di02</U></B></B>
<B>Applies To: di03, OneState</B>
<B><B>Define Line Item: Sum</B></B>
Date Type: Number
Formula: Box.Value[SUM: Lists to OneState.'di01 & di02']
<B>Define Line Item: Average</B>
Date Type: Number
Formula: Box.Value[AVERAGE: Lists to OneState.'di01 & di02']
<B>Define Line Item: Max</B>
Date Type: Number
Formula: Box.Value[MAX: Lists to OneState.'di01 & di02']
<B>Define Line Item: Min</B>
Date Type: Number
Formula: Box.Value[MIN: Lists to OneState.'di01 & di02']
<B>Cell Count: 200</B>
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.Tue, 04 Aug 2015 01:26:50 GMTPrevContributor2015-08-04T01:26:50ZAdHoc Subsets as an alternate to Select function
https://community.anaplan.com/t5/Anaplan-Platform/AdHoc-Subsets-as-an-alternate-to-Select-function/m-p/1575#M2528
<P>Hi, From time to time, it may be convenient to create an adhoc subset as an alternative for the SELECT Function. <U><STRONG>Business case :</STRONG></U></P>
<UL>
<LI>Source module : full list</LI>
<LI>Target Module : need to select only one line item (or more than one) from the source module.</LI>
</UL>
<P><FONT size="2"><U><STRONG>Proposals :</STRONG></U></FONT></P>
<UL>
<LI><FONT size="2">Option One:: </FONT>Formula in the target module will look as follow : Source.line item[SELECT: Full List.Relevant item]</LI>
<LI><FONT size="2">Option Two : use the subset in the target module. </FONT><FONT size="2">Formula will be easier : Source.line item.</FONT></LI>
</UL>
<P><FONT size="2">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 </FONT> I call the option two "Select Alternative for Lazy Peoples". Kind regards. Michel</P>Thu, 01 Nov 2018 02:21:46 GMThttps://community.anaplan.com/t5/Anaplan-Platform/AdHoc-Subsets-as-an-alternate-to-Select-function/m-p/1575#M2528PrevContributor2018-11-01T02:21:46ZRE: AdHoc Subsets as an alternate to Select function
https://community.anaplan.com/t5/Anaplan-Platform/AdHoc-Subsets-as-an-alternate-to-Select-function/m-p/1576#M2529
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.
<B><U>Define Lists: di01, di02, di03</U></B>
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.
<B><U>Define List: OneState</U></B>
One element, namely, 'State'. This list also need not have a top level item.
<B><U>Define Module: Box</U></B>
<B>Applies to: di01, di02, di03</B>
<B>Define Line Item: Value</B>
Data Type: Number
Fill this Box module with random data.
<B>Cell Count: 100,000</B>
<B><U>Define Module: Lists to OneState</U></B>
<B><B>Applies to: N/A</B></B>
<B>Define Line Item: di02</B>
Applies to: di02
Data Type: OneState
Formula: OneState.State
<B>Define Line Item: di01 & di02</B>
Applies to: di01, di02
Data Type: OneState
Formula: OneState.State
<B>Cell Count: 2,100</B>
<B><U>Define Module: Collapse di02</U></B>
<B><B>Applies To: di01, di03, OneState</B>
<B>Define Line Item: Sum</B></B>
Date Type: Number
Formula: Box.Value[SUM: Lists to OneState.'di02']
<B>Define Line Item: Average</B>
Date Type: Number
Formula: Box.Value[Average: Lists to OneState.'di02']
<B>Define Line Item: Max</B>
Date Type: Number
Formula: Box.Value[Max: Lists to OneState.'di02']
<B>Define Line Item: Min</B>
Date Type: Number
Formula: Box.Value[Min: Lists to OneState.'di02']
<B>Cell Count: 4,000</B>
<B><B><U>Define Module: Collapse di01 & di02</U></B></B>
<B>Applies To: di03, OneState</B>
<B><B>Define Line Item: Sum</B></B>
Date Type: Number
Formula: Box.Value[SUM: Lists to OneState.'di01 & di02']
<B>Define Line Item: Average</B>
Date Type: Number
Formula: Box.Value[AVERAGE: Lists to OneState.'di01 & di02']
<B>Define Line Item: Max</B>
Date Type: Number
Formula: Box.Value[MAX: Lists to OneState.'di01 & di02']
<B>Define Line Item: Min</B>
Date Type: Number
Formula: Box.Value[MIN: Lists to OneState.'di01 & di02']
<B>Cell Count: 200</B>
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.Tue, 04 Aug 2015 01:26:50 GMThttps://community.anaplan.com/t5/Anaplan-Platform/AdHoc-Subsets-as-an-alternate-to-Select-function/m-p/1576#M2529PrevContributor2015-08-04T01:26:50ZRE: AdHoc Subsets as an alternate to Select function
https://community.anaplan.com/t5/Anaplan-Platform/AdHoc-Subsets-as-an-alternate-to-Select-function/m-p/1577#M2530
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.Tue, 04 Aug 2015 02:21:01 GMThttps://community.anaplan.com/t5/Anaplan-Platform/AdHoc-Subsets-as-an-alternate-to-Select-function/m-p/1577#M2530PrevContributor2015-08-04T02:21:01Z