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
Dynamic Subset with Expand Above
-
- MVP
- Posts: 2832
- 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
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.
- PavoGa
- MVP
- Posts: 617
- 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
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:
2) Create a new subset with Expand Above off using the following code:
That should do it.
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]})
Code: Select all
{[P&L Dim].[Net Profit]} + [P&L Dim].[subDetailExpandedUp]
Ty
Cleveland, TN
Cleveland, TN
-
- 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
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
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