Skip navigation.
spilling the beans

subset editor

Intersect

developers
| |

Intersect returns only members that appear in both of two sets. One example might be to show products that performed well both last year and this year, or customers that are both high volume and high margin. The default is to drop duplicates although “, ALL” can be added if these are required.


Union

developers
| |

Union joins two sets together, returning the members of each set, optionally retaining or dropping duplicates (default is to drop).

This creates a single list of the top 5 and worst 5 products.
{UNION(


Head, Tail and Subset

developers
| |

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.


Filtering by CurrentMember, NextMember, PrevMember, Ancestor and FirstSibling

developers
| |

This example returns the members that have an Amount value in the Test cube above 18. The [Product].CurrentMember part is optional here but it makes the next example clearer.
{FILTER( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Product] )}, 0)},


Lag, Lead, NextMember, PrevMember, FirstSibling, LastSibling, Siblings and LastPeriods

developers
| |

Lags and Leads are the equivalent of Dnext/Dprev.
{ [Date].[2006-10-03].Lead(1) }
will return 2006-10-04.

Lead(n) is the same as Lag(-n) so either function can be used in place of the other by using a negative value, but if only one direction will ever be needed in a given situation then you should use the correct one for understandability’s sake. Note that they only return a single member so to return the set of members between two members you can use the lastperiods function.


Syndicate content