Head, Tail and Subset

Submitted by admin
on Wed, 2019-11-20 10:55

Where TopCount and BottomCount sort the values automatically and chop the list to leave only the most extreme values, Head combined with Filter works in a similar manner but Head then returns the FIRST members of the filtered set in their original dimension order.

These queries simply return the first and last members of the Product dimension as listed when you hit the ‘All’ button:{Head ( TM1SubsetAll ( [Product] ) )}
{Tail ( TM1SubsetAll ( [Product] ) )}

This returns the actual last member of the whole Product dimension according to its dimix:{Tail(TM1SORTBYINDEX( {TM1DRILLDOWNMEMBER( {TM1SUBSETALL( [Product] )}, ALL, RECURSIVE )}, ASC))}

An example of Tail returning the last member of the Customer Lending hierarchy:{Tail(TM1DRILLDOWNMEMBER( {[Product].[Customer Lending]}, ALL, RECURSIVE ))}

An example of Head returning the first 10 members (according to the dimension order) in the product dimension that have an Amount in the Test cube above zero.{HEAD( FILTER( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Product] )}, 0)}, [Test].([Posting Measures].[Amount]) > 0 ), 10)}

With both Head and Tail the “,10” part can actually be omitted (or just use “,0”) which will then return the first or last member.

One example of when this is useful over TopCount or BottomCount – i.e. when sorting the results would be detrimental - would be to return the last day the year when a certain product was sold.

Subset is closely related to Head and Tail, and can actually replicate their results, but is additionally capable of specifying a start point and a range, similar in concept to substring functions (e.g. SUBST) found in other languages, though working on a tuple of objects not strings.

The equivalent of Head, 10 would be:{Subset ( {Tm1FilterByLevel(TM1SubsetAll ( [Product] ) , 0)}, 1, 10)}
But Subset would also allow us to start partitioning the list at a point other than the start. So for example to bring in the 11th – 20th member:{Subset ( {TM1FilterByLevel(TM1SubsetAll ( [Product] ) , 0)}, 11, 10)}
Note that asking for more members than exist in the original set will just return as many members as it can rather than an error message.