Performing Summary and Compare with Logic Script

[Former Member]

Hi Experts,

I am attempting to create a script that compares the sum of two sets of
accounts and then perform an adjustment based on the result.  Appreciate any logicscript suggestions:

Logic:

If sum of Account set A > sum of Account Set B then record -1 for
Account Set B accounts (or one of them within set B) and *1 to specific account
within Account set A.  If Set B > A
then opposite logic.

Thought to use an alternate hierarchy to group accounts or a
property/properties to compare.

Having trouble summarizing amounts and then performing the compare….

Based on prior postings I would think to create a script that sums the
total maybe

*REC(EXPRESSION=%VALUE_A%,ACCOUNT="PARENT_SET_A",AUDITID="SOLO
PARENT"="

     *REC(EXPRESSION=%VALUE_B%,ACCOUNT="PARENT_SET_B",AUDITID="SOLO
PARENT"="

   I have examples to use for recording
the adjustment, appreciate any help with totaling and comparing.

Thanks

Eyal Feiler

[former_member186338 · Accepted solution]

Hi Eyal,

%VALUE_A% - is definitely an incorrect syntax.

To check: Account set A - has parent PARENT_SET_A? Same for B?

Vadim

[Former Member · Accepted solution]

Hi Vadim,

Yes, I created an alternate hierarchy with ParentSetA and parentSetB.

2) Thought to create 2 property sets if helpful Property1 that covers Set A and Set B and property2 to isolate set A and B then use properties to sum and compare.

Not sure the direction whether account parents or properties, and if so the syntax.

Thank you.

Eyal

[former_member186338 · Accepted solution]

Can you explain once more the exact logic with some sample and without "or". Because this sentence is unclear "

If sum of Account set A > sum of Account Set B then record -1 for

Account Set B accounts (or one of them within set B) and *1 to specific account

within Account set A.  If Set B > A

then opposite logic."

Vadim

[Former Member · Accepted solution]

Hi Vadim,

Here is an example with two scenarios.

DATA SET:

Parent #1 - 116000 Current Tax Assets (CTA) = 100

Parent #2 - 216000 Current Tax Liabilities (CTL) = (75)

Where AuditID = parent all solo 3050

Parent 116000 includes members:

116100

116200

116300

Parent 216000 includes members:

216100

216200

216300

If sum of parent 1 & 2 is > 0 then…. Record the following:

RECORD:
216100 75  AuditID 3025 Adjustments to solo

116100 (75)  AuditID 3025 Adjustments to solo

End results 116000 parent contains 100-75 = 25

DATASET2:

If sum of parent 1 & 2 is < 0 then…. Record the following:

Parent 116000 Current Tax Assets (CTA) = 100

Parent 216000 Current Tax Liabilities (CTL) = (125)

RECORD2:

116100 (100)  AuditID 3025 Adjustments to solo

216100 100  AuditID 3025 Adjustments to solo

End results 216000 parent contains (125)+100 = 25

Regards,

Eyal

[former_member186338 · Accepted solution]

Like this:

*XDIM_MEMBERSET ACCOUNT=116100 // only one account!

*XDIM_MEMBERSET AUDITID=3050 // not sure,"Where AuditID = parent all solo 3050" - base member?

*WHEN_REF_DATA = MASTER_DATA // to ensure the loop will happen even if 116100 is empty

*WHEN ACCOUNT

*IS * //already scoped

*REC(EXPRESSION=([ACCOUNT].[116000]+[ACCOUNT].[216000])>0 ? -[ACCOUNT].[216000] : [ACCOUNT].[116100],ACCOUNT="216100",AUDITID="3025")

*REC(EXPRESSION=([ACCOUNT].[116000]+[ACCOUNT].[216000])>0 ? [ACCOUNT].[216000] : -[ACCOUNT].[116100],ACCOUNT="116000",AUDITID="3025")

*ENDWHEN

B.R. Vadim

[Former Member · Accepted solution]

Vadim,

Thanks!

I will check and respond. FYI AuditIDs noted is a parent.

Eyal

[former_member186338 · Accepted solution]

If it's parent then the script will not work!

[former_member186338 · Accepted solution]

Please provide the ID for parent AUDITID - is it 3050 or?

Provide the ID of some base AUDITID under this parent.

Hope that 3025 is a base AUDITID?

Vadim

[Former Member · Accepted solution]

Hi Vadim,

The selection is inclusive of multiple AUDITID in the tree.  Here is an example (T - total tree node V is posting value).  This is a partial tree but you get the idea.

AD3000 - All solo activity (T)

  AD3050 All Reclassifications (T)

    AD3055 reclass (V)

       AD3400 Manual Adjustments (T)

         AD3410 Adjust 1 (V)

         AD3420  Adjust 2 (V)

              .........

                  INPUT

Does this answer your question?  Is it possible to write the script as we require.

Again, thank you for your time.

Eyal

[former_member186338 · Accepted solution]

Then the script will be:

*XDIM_MEMBERSET ACCOUNT=116100 // only one base account!

*XDIM_MEMBERSET AUDITID=AD3420 // have to be a base member

*WHEN_REF_DATA = MASTER_DATA // to ensure the loop will happen even if 116100 is empty or AD3420 is empty

*WHEN ACCOUNT

*IS * //already scoped

*REC(EXPRESSION=(([ACCOUNT].[116000],[AUDITID].[AD3050])+([ACCOUNT].[216000],[AUDITID].[AD3050]))>0 ? -([ACCOUNT].[216000],[AUDITID].[AD3050]) : ([ACCOUNT].[116100],[AUDITID].[AD3050]),ACCOUNT="216100",AUDITID="3025")

*REC(EXPRESSION=(([ACCOUNT].[116000],[AUDITID].[AD3050])+([ACCOUNT].[216000],[AUDITID].[AD3050]))>0 ? ([ACCOUNT].[216000],[AUDITID].[AD3050]) : -([ACCOUNT].[116100],[AUDITID].[AD3050]),ACCOUNT="116000",AUDITID="3025")

*ENDWHEN

Vadim

[former_member186338 · Accepted solution]

Actually it's not the best code in terms of performance due to this statement:

*WHEN_REF_DATA = MASTER_DATA

it will affect all dimensions...

It's better to create 2 additional base accounts on the root level and aggregate 116000 and 216000 amounts in this base members as a first step. Then in the next WHEN/ENDWHEN loop - compare and write results.

Vadim

[Former Member · Accepted solution]

Hi Vadim,

Thank you for your help.  We built the logic and tweaked slightly.  Currently testing and verifying - looks good!

I'll post the result once I see it's stable.

Regards,

Eyal

[Former Member · Accepted solution]

Hi Vadim,

We are having some issues with the script.  Is there a way to use the conditions we discussed above in order to execute the Account Based Calculation (ABC) rule?

Please see the attached portions of 2 scripts - one written based on the above discussion, and below it - separate to execute an ABC rule.  I would like to add the condition from the first e.g. >0 to execute ABC rule 1and <0 to execute ABC rule 2.

Can you help with the syntax?
Thanks

Eyal

ReclassExample.txt.zip

[former_member186338 · Accepted solution]

Hi Eyal,

There is NO conditional execution of the code in script logic. No way!

B.R. Vadim