Dynamic Subset with Expand Above

Post Reply
kenship
Posts: 95
Joined: Thu May 30, 2013 1:41 pm
OLAP Product: Cognos
Version: TM1 10.2
Excel Version: 2010

Dynamic Subset with Expand Above

Post by kenship » Tue May 15, 2018 12:48 pm

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

User avatar
tomok
MVP
Posts: 2430
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 » Tue May 15, 2018 1:16 pm

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
Community Contributor
Posts: 185
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 fixpack 7
Excel Version: 2013
Location: Cleveland, Tennessee

Re: Dynamic Subset with Expand Above

Post by PavoGa » Tue May 15, 2018 2:02 pm

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
Posts: 95
Joined: Thu May 30, 2013 1:41 pm
OLAP Product: Cognos
Version: TM1 10.2
Excel Version: 2010

Re: Dynamic Subset with Expand Above

Post by kenship » Tue May 15, 2018 2:18 pm

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