Calculation Restricting measures based on Attribute

[Deb_]

Hello
I want to build the following story calculation : "all non intercompany accounts for G&A functional area"

My Account/Measure dimension has an Y/N attribute to define if an account is intercompany or not. (the Y/N are set on the node level)
I want to create a restricted calculation that will aggregate all the non-Intercompany accounts for a specific functional area.

All my accounts are hierarchy set under "all account" parent, therefore I can easily choose "All account", when building a restricted measure - but then I'm stuck:
1. I tried to use this restricted calculation and I added on the table level a filter for this interco-attribute- however the result became NULL
2. I tried to work directly on the calculation, however I cannot filter out an attribute based to the Measure/Account Dimension
(see below Print screen)
3. I tried to use a calculated dimension and had the same issues (no attribute for account dimension).

4. then I decided to create a data action that would filter out those Interco-account on a statistic account. however for some reason the data action I've build didn't work. (it did calculate but the result didn't filter out the inter company - I have no idea why)

MEMBERSET[d/ACCOUNT]=BASEMEMBER([d/ACCOUNT].[h/parentId] ,"ALL" )

IF [d/ACCOUNT].[p/P_INTERCO]="N"
THEN
DATA([d/ACCOUNT]="NON_IC_ACCOUNTS")=
RESULTLOOKUP()	
ENDIF

I'm completely lost I have no more ideas how to work around to make it happen

thank you

[hartmut_koerner]

Hi Deb,

the crucial point seems to be that "the Y/N are set on the node level". Attribute values are not inherited along the hierarchy. I think you need to maintain them on the leave level for your scenario.

With that, 1 and 4 should work. 2 and 3 seem to be a limitation of the system. There are several places where attribute filtering still does not work.

Best regards
Hartmut

[Deb_]

Sorry I wrote node and I meant Leaves - It's on the leaf level of course and nothing work

[hartmut_koerner]

Hi Deb,

not sure if I got your point. As said, you need to set for each account on the leave level the Y/N attribute value. Isn't it the case that one account either is an I/C account or not? Or does this depend on another dimension (group?). Then we anyway have a problem and need to go for another solution besides working with an account attribute.

Best regards

Hartmut

[Deb_]

I have an account hierarchy beginning on:

All account
then I have few level of grouping
and the leaf is the "GL account" and in can be OR Y OR N on the Interco-indicator attribute (it cannot be both of course)

every thing is "by the book" that's why I'm surprised that it doesn't work

I hope that I made it clear 🙂

[Deb_]

Hartmut!!!!
I found a bug!
option 1 (adding on the table level a filter for this interco-attribute-giving the result NULL)

what I did was excluding out "Y" on my filter and I got NULL
I just change it to filter in "N" - It Works!!!

I won't open an OSS cause I don't have time - can you please let know however can correct this strange bug

thank you for your precious time.
let's meet on my next Question 🙂

[Deb_]

also option 4 I tried !="Y" and ="N"

both don't work

[William_Yu1]

Hi dd5,

Only fly calculation probably won't work for this type of aggregation, but maybe you try with below simple script.

MEMBERSET [d/Account].[p/P_INTERCO] = "N"

DATA([d/Account] = "NON_IC_ACCOUNTS") = RESULTLOOKUP()

Best regards,. William

[Deb_]

it doesn't filter out the interco 😞

[William_Yu1]

at least it works for me... you need to check your model then...

[Deb_]

It does?
gonna re-check
although my model seems to be very stable and well build it handles dozens of DA/ALLOC/on the fly and more

nut I found a bug on number 1 - so I'll use it
thank you William!!