Dynamic Subset with Expand Above

Post Reply
kenship
Regular Participant
Posts: 194
Joined: Thu May 30, 2013 1:41 pm
OLAP Product: Cognos
Version: Planning Analytics 2.0.7
Excel Version: 2010

Dynamic Subset with Expand Above

Post by kenship »

Hi,

I'm working on a dynamic subset and couldn't figure out what's wrong.

I have a P&L dimension.
(Net Profit)
> Revenue
> Gross Exp

Now there's a special request in that they want to see:

(Net Profit)
> Revenue item 1
> Revenue item 2
...
> Revenue

> Gross Exp item 1
> Gross Exp item 2
...
> Gross Expenditure


So the way I create this is using dynamic subset, turn on Expand Above, expand all the items and move the "(Net Profit)" from bottom to top.

Problem is: after saving it the "(Net Profit)" went back to the bottom.

I have seen other subset successfully doing what I need here but can't figure out how to do it.

Any advice is appreciated, thanks!

Kenneth
tomok
MVP
Posts: 2831
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: Dynamic Subset with Expand Above

Post by tomok »

kenship wrote: Tue May 15, 2018 12:48 pm So the way I create this is using dynamic subset, turn on Expand Above, expand all the items and move the "(Net Profit)" from bottom to top.

Problem is: after saving it the "(Net Profit)" went back to the bottom.
That's because a "dynamic" subset is just that, dynamic. It recalculates itself every time you use it. Defining it as expand above means Net Profit is going to be at the bottom. You can't cherry pick one element and move it somewhere else and leave it dynamic. I tried an MDX query on Net Profit that drilled on Net Profit, then excluded Net Profit and then I added Net Profit back as an individual member but that didn't work. The expand above kept making it go to the bottom regardless of whether I added the individual Net Profit member first or last.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/
User avatar
PavoGa
MVP
Posts: 616
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.9.1
Excel Version: 2013 PAW
Location: Charleston, Tennessee

Re: Dynamic Subset with Expand Above

Post by PavoGa »

This may do what you want:

1) create and save a subset named subDetailExpandedUp (or whatever you want) with Expand Above on your dimension with this code:

Code: Select all

EXCEPT( TM1DRILLDOWNMEMBER({[P&L Dim].[Net Profit]}, ALL, RECURSIVE),
    {[P&L Dim].[Net Profit]})
2) Create a new subset with Expand Above off using the following code:

Code: Select all

{[P&L Dim].[Net Profit]} + [P&L Dim].[subDetailExpandedUp]
That should do it.
Ty
Cleveland, TN
kenship
Regular Participant
Posts: 194
Joined: Thu May 30, 2013 1:41 pm
OLAP Product: Cognos
Version: Planning Analytics 2.0.7
Excel Version: 2010

Re: Dynamic Subset with Expand Above

Post by kenship »

Thanks for the reply.

For now my solution is break the relationship of the "(Net Profit)" by skipping a level in Gross Expenditures and Revenues. In doing so the dynamic subset reads each rollup separately and allow me to insert "(Net Profit)" as a separately item instead of it being related in the subset.

Of course this is not a good solution, but at least it gives me what I'm looking for.

I'll try the MDX as well.

Thanks again.

Kenneth
Post Reply