TM1 Forum

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

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.

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.

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