Best way to sum multiple members in default logic?

[alexander_b_white]

What is the best way to sum up multiple members in default logic and store the results in a separate member?

This is easy for the Account dimension since all Accounts are automatically included in default logic, but what about for other dimensions?

For example, let's say we have a user-defined dimension EXAMPLEDIM, and we want to sum up MEMBER1, MEMBER2, and MEMBER3 and store the results in MEMBERX.  We want this to work in default logic so that the values are summed whenever someone submits a record to any of the 3 members.  Seems simple enough, right?

At first, one would think this could work:

*WHEN EXAMPLEDIM

*IS MEMBER1, MEMBER2, MEMBER3

*REC( EXPRESSION=%VALUE%, EXAMPLEDIM="MEMBERX")

*ENDWHEN

The problem with this is that if a user were to enter a value of 100 into MEMBER1, then a value of 100 would be written to MEMBERX, regardless of whether or not MEMBER2 and MEMBER3 contain a preexisting value.

Okay, so we can preface this with some scoping, right?

*XDIM_MEMBERSET EXAMPLEDIM = MEMBER1, MEMBER2, MEMBER3

While that would work, it would lead to this calculation being performed every time someone triggers default logic.  One can imagine how this would degrade the performance of a Model if many such calculations were being performed in default.

Another approach would be to write a separate REC statement for each member like this:

*WHEN EXAMPLEDIM

*IS MEMBER1

*REC( EXPRESSION=%VALUE% + GET(EXAMPLEDIM="MEMBER2") + GET(EXAMPLEDIM="MEMBER3"), EXAMPLEDIM="MEMBERX")

*IS MEMBER2

*REC( EXPRESSION=%VALUE% + GET(EXAMPLEDIM="MEMBER1") + GET(EXAMPLEDIM="MEMBER3"), EXAMPLEDIM="MEMBERX")

*IS MEMBER3

*REC( EXPRESSION=%VALUE% + GET(EXAMPLEDIM="MEMBER1") + GET(EXAMPLEDIM="MEMBER1"), EXAMPLEDIM="MEMBERX")

*ENDWHEN

But this could lead to doubling of data if the user were to submit values to more than one of these members at a time.  Plus, can you imagine trying to maintain this for more than a handful of members?

Likewise, we could try referencing the destination member in our expression:

*WHEN EXAMPLEDIM

*IS MEMBER1, MEMBER2, MEMBER3

*REC( EXPRESSION=%VALUE% + GET(EXAMPLEDIM="MEMBERX"), EXAMPLEDIM="MEMBERX")

*ENDWHEN

But this could lead to the same doubling problem if the user submits a record to more than one of the source members.

Do you have any ideas?  Note that we cannot use a hierarchy or dimension logic since the requirement is to calculate and store the results, since hierarchies and calculated members can often change.

Thanks!

Alex

[alexander_b_white]

Does anyone have further ideas?  I am very curious as to whether there is an elegant solution for this.

To summarize, the requirements are as follows:

• Sum up several members and store the result in another member
• This should run in default logic so that the sum is always up-to-date
• The logic should only be triggered when values are submitted to the relevant source members (i.e., we don't want this to run every time default logic is invoked)
• This should function correctly regardless of whether the user has submitted values to all of the source members, or only one of the source members

Cheers,

Alex

[Former Member]

Hi,

I would add a new property to EXAMPLEDIM and for each base member identify the member which should show the sum. So for your MEMBER1, MEMBER2 and MEMBER3 this new property would always show MEMBERX.

Then I would use a script like

*WHEN EXAMPLEDIM

*IS *

*REC(EXPRESSION=%VALUE%,EXAMPLEDIM=EXAMPLEDIM.NEWPROPERTY)

*ENDWHEN

BR,
Arnold

[former_member186498]

Hi Alex,

Okay, so we can preface this with some scoping, right?

*XDIM_MEMBERSET EXAMPLEDIM = MEMBER1, MEMBER2, MEMBER3

While that would work, it would lead to this calculation being performed every time someone triggers default logic.  One can imagine how this would degrade the performance of a Model if many such calculations were being performed in default.

never use *XDIM_MEMBERSET in default logic because this force a scope and default logic is triggered every time someone send data to DB.

Use *XDIM_FILTER in default logic before the *WHEN instruction.

Regards

     Roberto

[alexander_b_white]

Agreed!  Using an *XDIM_MEMBERSET statement would force this particular *COMMIT section to run every time any records are passed through default logic, whether they are relevant to this calculation or not.  For that reason, this approach is not ideal.

*XDIM_FILTER is certainly useful for limiting which parts of default logic are triggered.  However in this scenario we would have to assume that the user had simultaneously submitted values for MEMBER1, MEMBER2, and MEMBER3, which may not always be the case.

We could also use an *XDIM_ADDMEMBERSET statement, but this would have a similar effect as the *XDIM_MEMBERSET statement (i.e., causing the logic to run regardless of whether relevant records were submitted to the database).

Ideally, we would have some way to conditionally add members to the record set.  For example:

*WHEN EXAMPLEDIM

*IS MEMBER1, MEMBER2, MEMBER3

*XDIM_ADDMEMBERSET EXAMPLEDIM = ACCOUNT1, ACCOUNT2, ACCOUNT3

*REC( EXPRESSION=%VALUE%, EXAMPLEDIM="MEMBERX")

*ENDWHEN

But obviously this is not a permissible script.