MDX Q

[Steve Rowe]

Afternoon, seeing as no one else is going to post anything interesting this afternoon, I've a quick question.

Definition of dynamic subset
{TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER( { {TM1SubsetBasis()}, { [Sub-analysis 1].[GTPL Balance] } }, ALL, RECURSIVE )}, 0)}

What I want is all the 0 level children of the element consolidation "GTPL Balance". GTPL balance is a level 1 consolidation with just 0 level elements under it (by definition). I think the MDX is correct and at first look I appear to be getting what I want.

But

On a harder look I see that every element is repeated three times in the list. So I think the MDX is wrong
On an even harder look I see that if I expand the hierarchy under GTPL Balance I see that every element is under the consolidation 3 times.

....

I didn't think it was possible to have an element under a single consolidation more than once?

Anyway perfect time to restart the server and pack up for the weekend....

Verwsion 845 latest.
Bonne weekened mes amis!


...........

Edit : Got the result I needed by changing the MDX by removing the "TM1SubsetBasis()" from the expression.

{TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER( { { }, { [Sub-analysis 1].[GTPL Balance] } }, ALL, RECURSIVE )}, 0)}

If any one fancies explaining what was going on I would be grateful, I really must get that MDX book.....
Cheers

[Steve Vincent]

subset basis isn't required if you are manually defining the "start point" as you are. its a bit of an oddity as nowhere in the dim or the mdx does it show what you picked as the subset basis. the only place i have found it is in the .sub using a text editor.

as for the same element multiple times in one consolidation thats definately OK. Each one could have a different weight so there is no reason why you can't do that. What it does stop you from doing is creating the same element more than once, but you can copy / paste the element as many times as you wish

[Martin Ryan]

Each one could have a different weight so there is no reason why you can't do that.

Why would you want to have the same element in the same consolidation with different weights? Wouldn't that just confuse the issue? Wouldn't it be better to do the maths and make the element exist once with a weighting that is a net figure?

I was surprised by Steve R's result, because there's many a time where I've run a dimensionelementcomponentadd over the same dimension, parent and child, and only had the child show up once - as I'd intended.

I can't see how it would ever be useful to have the same child repeated under the one parent.

I also can't understand how Steve R has managed to do it!

Martin

[Steve Vincent]

i admit it doesn't come up often, but it has. Imagine a consolidation with lots of sub consolidations covering skill types, some manual some professional. And imagine the lowest level tags have no way of programatically telling the difference between the 2 and someone asks for the total of professional only. You can go about it different ways, you could just build a 2nd hierarchy without those tags (after manually figuring out which ones) or you could copy the original one and as them as -1 weighting. In Steve's example that 2nd option would have shown some elements twice.

Admitadly its a pig to "read" in the subset editor, but what isn't? :p

[Steve Rowe]

Just to clarify on the multiple children issue. They were under the same direct parent not under sub consolidations, I think y'all are taking crocodiles and alligators....

[Eric]

Not a fan of the same element with different weights. I inherited this a few years ago and it gave me a headache.
Example

Net Sales
.|
.- Gross Sales
....|
....-Deductions
......|
......-Returns
........|
........- Cash Discount
...........|
...........- Gross Sales (Weight -0.02)

I suppose there is benefit is in performance with using the weight feature, but not enough benefit to support confusing everyone


BTW. indenting?

[Martin Ryan]

Yes, not the tidiest, but also not what Steve is saying. Steve has the ONE parent with the SAME child appearing multiple times.

By way of the universal example of a P&L:

Profit
-> Costs
-> Sales
-> Costs

So profit is ending up as sales - costs - costs. No intermediary parents, just one parent, with the same child under it twice.

Martin

[Steve Vincent]

Not necessarily the way you should do it, but one use of that consolidation might be a what-if;

"What if my costs were to increase by 2.5% on current figures?"

Use the weight of 0.025 to see the affect on the total profit. Certainly not how i'd go about it, but as with many a thing in TM1 there is more than one way to do it. If you had no clue about rules its also an interesting alternative