Null Records in scope when comparing category data

[former_member404622]

I have a (what i thought would be a simple) script to compare 2 sets of data in 2 category members.

*SELECT (%TIME%,"[ID]", TIME, "[CURRENTPERIODS] = 'CURRENT_MONTH'")
*XDIM_MEMBERSET TIME = %TIME%
*XDIM_MEMBERSET ACCOUNT AS %ACCT% = BAS(ACCOUNT)
*XDIM_MEMBERSET ACCOUNT = %ACCT%
*XDIM_MEMBERSET CATEGORY = V1
*XDIM_ADDMEMBERSET CATEGORY = V2

*WHEN CATEGORY
*IS V1
*REC(EXPRESSION = %VALUE% - ([CATEGORY].[V2]), ACCOUNT = DELTA, VERSION = V2)
*ENDWHEN

Script works great when there is a value in V1 but when their is nothing in V1 but data in V2 i don't get

a resulting value.  How can I force/trick it to see a value in V1 (even 0) so i will get a result?

[former_member186338]

Hi Jim,

Variants:

1) 2 loops will do the job!

*SELECT (%TIME%,"[ID]", TIME, "[CURRENTPERIODS] = 'CURRENT_MONTH'")
*XDIM_MEMBERSET TIME = %TIME%
*XDIM_MEMBERSET ACCOUNT AS %ACCT% = BAS(ACCOUNT)
*XDIM_MEMBERSET ACCOUNT = %ACCT%

*XDIM_MEMBERSET CATEGORY = V1

*WHEN CATEGORY
*IS *
*REC(EXPRESSION = %VALUE% - ([CATEGORY].[V2]), ACCOUNT = DELTA, VERSION = V2)
*ENDWHEN

*XDIM_MEMBERSET CATEGORY = V2

*WHEN CATEGORY

*IS *

*REC(EXPRESSION = ([CATEGORY].[V1]) - %VALUE%, ACCOUNT = DELTA, VERSION = V2)

*ENDWHEN

2) Single loop:

*SELECT (%TIME%,"[ID]", TIME, "[CURRENTPERIODS] = 'CURRENT_MONTH'")
*XDIM_MEMBERSET TIME = %TIME%
*XDIM_MEMBERSET ACCOUNT AS %ACCT% = BAS(ACCOUNT)
*XDIM_MEMBERSET ACCOUNT = %ACCT%

*XDIM_MEMBERSET CATEGORY = V1,V2

*WHEN CATEGORY
*IS V1
*REC(EXPRESSION = %VALUE%, ACCOUNT = DELTA, VERSION = V2)

*IS V2

*REC(EXPRESSION = -%VALUE%, ACCOUNT = DELTA, VERSION = V2)

*ENDWHEN

Vadim

P.S. Second variant is better for this particular case (expression contains only "+" and "-" operators) but first one is universal. There is a third option - to use *WHEN_REF_DATA = MASTER_DATA  with the original code but I don't recommend it due to performance issues.

Message was edited by: Vadim Kalinin - P.S. Added

[former_member404622]

Thanks Vadim.

I also tired something like that but the 2nd loop overwrites what happens in the 1st loop. example:

Product_1 has a value in both categories and the expected delta is 2864.7 that will be in V2 Delta member.  Sum of 7Z+Delta = 2748.17 which is V1 amount so that would be correct.

But when the 2nd loop runs it takes the V2 total value (2748.17 after 1st loop) and subtracts the V1 value and puts the delta to zero.

Product_2 only has a value in V2 so its not scoped in the 1s loop and thus it comes out ok from loop 2.

Sure i am missing something easy, think i have been looking at it for too long.  seems so easy to get V1=V2 yet......

Jim

[former_member186338]

Ups, looks like your DELTA Account is included in BAS(ACCOUNT)

Then add to the loops:

*WHEN ACCOUNT

*IS<> DELTA

*WHEN CATEGORY

*IS *

*REC(EXPRESSION = %VALUE% - ([CATEGORY].[V2]), ACCOUNT = DELTA, VERSION = V2)

*ENDWHEN

*ENDWHEN

same for other loops

or

*XDIM_MEMBERSET ACCOUNT AS %ACCT% = BAS(ACCOUNT)

*SELECT(%ACC%,"[ID]",ACCOUNT,"[ID]=%ACCT% AND [ID]<>DELTA"

*XDIM_MEMBERSET ACCOUNT = %ACC% // scope will be without DELTA!

Vadim

[former_member404622]

Closer, but that still scopes in the 7Z value of -116.53 in V2 thus and changing the 7X value again, during the second loop.

In a *REC statement does there have to be a number to compare to in a boolean expression? Was thinking of checking after the 1st loop, check where V1=V2 and don't act on those.

something like:

XDIM_MEMBERSET CATEGORY = V2 

*WHEN CATEGORY

*IS *

*REC(EXPRESSION = (([CATEGORY].[V1]) <>%VALUE%) * ([CATEGORY].[V1]) - %VALUE%), ACCOUNT = DELTA, VERSION = V2)

*ENDWHEN

[former_member186338]

Hi Jim,

Something strange is going on with your code. Some explanation required:

1. Please show the structure of ACCOUNT - what are base members, where DELTA is located in hierarchy etc.

2. The logic of the script is not 100% clear for me:

You have 2 versions, V1 has some aggregated value in the parent ACCOUNT (including DELTA)

You want to calculate the following:

SUM([V1],[All base members of ACCOUNT])-SUM([V2],[All base members of ACCOUNT except DELTA?])

And to store the result in ([V2], [DELTA])? At the end you want to have ([V1],[ACCOUNT]) and ([V2],[ACCOUNT]) - equal?

Vadim

[former_member404622]

Hi Vadim,

Structure:

Yes, we have 2 versions that are getting data from different sources and want to reconcilce the values.

V1 is the master.  So as you correctly said. At the end of the day I want V1 = V2.

Starting point looks like the following:

Product 1 had values in both source data and were loaded to V1 and V2.  To make

V1=V2 the delta value in V2 needs to be 2864.7.

Product 2 only had data from 1 source and was loaded to V2. To make V1=V2 the

delta value in V2 needs to be -556.

V1 will never have a delta value, but could have data in 7Z or 7Q.

Jim

[former_member186338]

Hi Jim,

Additional questions:

1. BPC version, core SP...

There is some bug in some old SP... and difference between 7.5 and 10

2. The ACCOUNT dimension structure is still not clear. What is the ACCTYPE of DELTA and other accounts under ACCOUNT and ACCOUNT itself? What is the parent of DELTA?

3. What is the result of the following code:

*XDIM_MEMBERSET ACCOUNT AS %ACCT% = BAS(ACCOUNT)

*SELECT(%ACC%,"[ID]",ACCOUNT,"[ID]=%ACCT% AND [ID]<>DELTA"

*XDIM_MEMBERSET ACCOUNT = %ACC% // scope will be without DELTA!

*SELECT (%TIME%,"[ID]", TIME, "[CURRENTPERIODS] = 'CURRENT_MONTH'")
*XDIM_MEMBERSET TIME = %TIME%

*XDIM_MEMBERSET CATEGORY = V1,V2

*WHEN CATEGORY
*IS V1
*REC(EXPRESSION = %VALUE%, ACCOUNT = DELTA, VERSION = V2)

*IS V2

*REC(EXPRESSION = -%VALUE%, ACCOUNT = DELTA, VERSION = V2)

*ENDWHEN

Vadim

[former_member404622]

Hi Vadim,

sorry got side tracked.  Going to switch to a BADI now for speed.  I am just the finance

guy that experiements to much .  I will read through the questions to ask but i don't get UJKT access which limits my fun.

Thanks

Jim

[former_member186338]

Ups, badi? What for? For this case the script is more than enough ...

[Former Member]

Yes, I def would go the BADI route

more control and way faster.

[former_member186338]

If your goal is to increase support costs then go with BADI