Signed Based Reclassification of Account

[Former Member]

Hi Experts,

I am facing a peculiar Client requirement. See below details.

Suppose I have the following members:

Account dimension:

MemberID      PARENTH1

0001               TOTAL_AST

0010               TOTAL_AST    

0002               TOTAL_LEQ

Interco Dimension:

MemberID     PARENTH1

I_NONE          TOTAL_INTERCO

I_E1                TOTAL_INTERCO

Values are as follows:

Account     Intercompany       Time            Signeddata

0001            I_NONE          2015.03            -40000.00

0001            I_E1                2015.03             30000.00

Therefore When I look at my Report I see the following:

Account                Intercompany              Time              Signeddata

TOTAL_AST        TOTAL_INTERCO     2015.03         -10000.00

TOTAL_LEQ        TOTAL_INTERCO     2015.03          0

As this value is Negative I want to zero it out in TOTAL_AST and move the Amount to TOTAL_LEQ.

So I use the following script:

[ACCOUNT].[#0010]= ([ACCOUNT].[0001]<0?[ACCOUNT].[0001]*-1:0)
[ACCOUNT].[#0002]= ([ACCOUNT].[0001]<0?[ACCOUNT].[0001]:0)

But result of this is as follows:

Account       Intercompany       Time     Signeddata

0001           I_NONE          2015.03          -40000.00

0001           I_E1                2015.03           30000.00

0010           I_NONE          2015.03           40000.00

0002           I_NONE          2015.03           40000.00

Again when I look at my report I see the following:

Account               Intercompany                   Time     Signeddata

TOTAL_AST       TOTAL_INTERCO         2015.03     30000

TOTAL_LEQ       TOTAL_INTERCO         2015.03     40000

My asset side is not zeroing out.

What I want to see is:

Account                Intercompany               Time      Signeddata

TOTAL_AST     TOTAL_INTERCO          2015.03     0

TOTAL_LEQ     TOTAL_INTERCO          2015.03     10000.00

Please advice.

[former_member186338 · Accepted solution]

Hi Collin,

It's better not to use MDX calculations like [ACCOUNT].[#0010]=... - extremely slow! Switch to WHEN/ENWHEN syntax.

Then you have to properly specify your requirements - which value you are checking for negative sign? And how do you want to zero it ...

Vadim

[Former Member · Accepted solution]

Hi Vadim,

I haven't tried WHEN/ ENDWHEN Syntax.

Since the script logic primer readily provided the syntax for Conditional Logic I went with MDX.

Secondly, The value I am trying to Check for Negative sign is:

Account "0001" (Base Member), Interco "TOTAL_INTERCO" (Parent Member).

What is happening is the *XDIM_MEMBERSET is not allowing for Parent Member i.e. TOTAL_INTERCO. Hence I am using BAS(TOTAL_INTERCO).

Because of this the MDX Formula is evaluating the  Children of TOTAL_INTERCO (i.e. I_NONE and I_E1) individually.

And since Value against Accout:0001 and  Interco:I_NONE is negative it is working correctly.

But Since value against Account:0001 and Interco: I_E1 is positive it is not working properly.

Thirdly, I am zeroing it at the common Parent (i.e. TOTAL_AST) between "0001" and "0002" by giving "0002" the equal and opposite value of "0001".

Please can you advice me how do I make the script evaluate the sign not at the base members but at the parent member.

Regards,

Collin.

[former_member186338 · Accepted solution]

"Because of this the MDX Formula is evaluating the  Children of TOTAL_INTERCO (i.e. I_NONE and I_E1) individually." - this is against your requirements:

"I am trying to Check for Negative sign is:

Account "0001" (Base Member), Interco "TOTAL_INTERCO" (Parent Member)."

"Since the script logic primer readily provided the syntax for Conditional Logic I went with MDX." - MDX syntax is absolutely bad idea. You can use conditions in WHEN/ENDWHEN also.

Vadim

[Former Member · Accepted solution]

Hi Vadim,

Yes. I know it is against my requirements.

"Because of this MDX Formula is evaluating the children..."  is the issue I need help with.

I could go with WHEN/ ENDWHEN but again how to make it work on "TOTAL_INTERCO" (Not on the children individually)?

Regards,

Collin.

[former_member186338 · Accepted solution]

Something like:

*REC(EXPRESSION=[INTERCOMPANY].[TOTAL_INTERCO]<0 ? %VALUE% : [ACCOUNT].[0002], ACCOUNT=0002)

Vadim

[Former Member · Accepted solution]

Hi Vadim,

Actually, TOTAL_INTERCO is only illustrative.

Actually there are multiple Dimensions which need to be evaluated at a Parent Level eg. TOTAL_SEGMENT, TOTAL_AUDITTRAIL etc.

Is it possible to Tuple multiple dimensions?

Thanks and Regards,

Collin.

[former_member186338 · Accepted solution]

Sample with Environment Shell (Planning model):

Account dimension:

MemberID      PARENTH1

BS122            BS120 (AST)

BS121            BS120 (AST)    

BS222            BS220 (LEQ)

Interco Dimension:

MemberID     PARENTH1

ThirdParty     TotalInterco

I_US              TotalInterco

Initial data:

Script:

*XDIM_MEMBERSET INTERCO=BAS(TotalInterco)

*XDIM_MEMBERSET ACCOUNT=BS122

*XDIM_MEMBERSET TIME=2014.01

*XDIM_MEMBERSET AUDITTRAIL=Input

*XDIM_MEMBERSET CATEGORY=Actual

*XDIM_MEMBERSET ENTITY=DE

*XDIM_MEMBERSET PRODUCT=ProductA

*XDIM_MEMBERSET RPTCURRENCY=LC

*WHEN ACCOUNT

*IS *

*REC(EXPRESSION=[INTERCO].[TotalInterco]<0 ? %VALUE% : [ACCOUNT].[BS121], ACCOUNT=BS222)

*REC(EXPRESSION=[INTERCO].[TotalInterco]<0 ? -%VALUE% : [ACCOUNT].[BS121], ACCOUNT=BS121)

*ENDWHEN

Result after script run:

Vadim

[former_member186338 · Accepted solution]

Yes, you can use tuple syntax!

([DIMNAME1].[PARENTMEM1],[DIMNAME2].[PARENTMEM2],...)<0 ? ...

Vadim

[Former Member · Accepted solution]

Thanks a lot Vadim.

It worked perfectly and quite fast too.

Regards,

Collin