cancel
Showing results for 
Search instead for 
Did you mean: 

*XDIM_NOSCAN not working with PRIOR Time keyword

0 Kudos

Greetings,

I am having some trouble with a logic script that seems to be ignoring the *XDIM_NOSCAN instruction.  I am running BPC 10.0 MS SP12.

Logic Script

The script below (simplified for testing purposes) is designed to add the Sales Account to the prior period's AUM Account value:

*XDIM_ADDMEMBERSET DATASRC=Calculation

*XDIM_ADDMEMBERSET TIME=PRIOR

*XDIM_NOSCAN TIME=PRIOR

*CALC_EACH_PERIOD

*WHEN ACCOUNT

   *IS Sales

      *REC(EXPRESSION= GET(ACCOUNT="AUM", DATASRC="Input", TIME=PRIOR) + GET(ACCOUNT="AUM", DATASRC="Calculation", TIME=PRIOR) - %VALUE%, ACCOUNT="AUM", DATASRC="Calculation")

*ENDWHEN

Script explanation

This script adds up three pieces of information:

  • Prior period AUM from the Input DataSrc (users would input this for the starting period)
  • Prior period AUM from the Calculation DataSrc (this would have been calculated by this very logic script)
  • Current period Sales (entered by the user) - this is subtracted in the script due to the Account Type

The result is stored in the AUM Account, Calculation DataSrc.

The first three lines of the script are required because the users will submit Sales via an input form, so the initial record set will only include the current time period and the Input DataSrc.  Thus the *XDIM_ADDMEMBERSET instructions are used to include the Calculation DataSrc and prior time periods in the record set.  Subsequently the *XDIM_NOSCAN instruction is used to prevent the script from recalculating the prior time period, despite it being included in the record set.

Sample data & results

Here's what's going on.  Let's say the user inputs initial AUM of $10,000 in July, and $50 of Sales in August-October:

AccountDataSrcTimeAmount
AUMInput2015.JUL$10,000
SalesInput2015.AUG$50
SalesInput2015.SEP$50
SalesInput2015.OCT$50

The script successfully calculates the expected results shown below:

AccountDataSrcTimeAmount
AUMCalculation2015.AUG$10,050
AUMCalculation2015.SEP$10,100
AUMCalculation2015.OCT$10,150

Now let's say a user wants to submit a new Sales value in October:

AccountDataSrcTimeAmount
SalesInput2015.OCT$100

Here's what we would expect the script to calculate:

AccountDataSrcTimeAmount
AUMCalculation2015.AUG$10,050
AUMCalculation2015.SEP$10,100
AUMCalculation2015.OCT$10,200

However, here's what's happening:

AccountDataSrcTimeAmount
AUMCalculation2015.AUG$10,050
AUMCalculation2015.SEP$50
AUMCalculation2015.OCT$150

My theory

My troubleshooting indicates that the script is ignoring the *XDIM_NOSCAN instruction.

The user had input Sales into October, and the *XDIM_ADDMEMBERSET instruction added September to the record set.  Despite the *XDIM_NOSCAN instruction telling the script not to recalculate September, it is still recalculating September.  This would not be an issue, if not for the fact that the record set does not contain August's AUM.  So, when trying to calculate September, the record set sees $0 for August AUM, adds that to the $50 Sales in September, and finds the result to the $50 instead of $10,100.

I have tried substituting the *XDIM_ADDMEMBERSET instruction with *XDIM_MEMBERSET TIME=%TIME_SET%,PRIOR, and substituting the *XDIM_NOSCAN instruction with *XDIM_NOSCAN TIME<>%TIME_SET%, but those do not seem to work either.  Do you have any idea how to make the *XDIM_NOSCAN instruction work in this scenario?

Accepted Solutions (1)

Accepted Solutions (1)

0 Kudos

To provide an update on this post, I was able to solve the problem using a slightly different approach.  Initially, I was trying to use the *XDIM_NOSCAN statement to control which Time periods would be calculated.  Since that was not working, I tried using a *WHEN/*ENDWHEN statement to control the Time periods being calculated, and it worked perfectly.  Using my initial example, the updated script would look like this:

*XDIM_ADDMEMBERSET DATASRC=Calculation

*XDIM_ADDMEMBERSET TIME=PRIOR

*CALC_EACH_PERIOD

*WHEN TIME

   *IS %TIME_SET%

      *WHEN ACCOUNT

         *IS Sales

            *REC(EXPRESSION= GET(ACCOUNT="AUM", DATASRC="Input", TIME=PRIOR) + GET(ACCOUNT="AUM", DATASRC="Calculation", TIME=PRIOR) - %VALUE%, ACCOUNT="AUM", DATASRC="Calculation")

      *ENDWHEN

*ENDWHEN

My real script has undergone a lot of additional changes as the requirements were refined throughout the prototyping process (for example, the script now runs within a package, so the scoping has changed substantially), but the underlying idea should be the same regardless.

Cheers,

Alex

Answers (3)

Answers (3)

cecilia_petersson2
Active Participant
0 Kudos

Hi Alex,

Ah, now I think I have the full picture! I've been testing back and forth using the Default logic and changing the October Sales Input figure. What I've found is that if I replace *XDIM_ADDMEMBERSET TIME=PRIOR with *XDIM_MEMBERSET TIME=PRIOR, I get the expected result.. So apparently the logic is including the period the user enters data in regardless of the time scope. It seems to have to do with using *CALC_EACH_PERIOD, because when commenting it out, I got an error about PRIOR being invalid (apparantly there was no current period to deduce PRIOR from).

So in short, try using *XDIM_MEMBERSET TIME=PRIOR instead.

/Cecilia

0 Kudos

Cecilia,

Thanks again for all your effort in assisting with this!  I took your suggestions and was able to replicate the behavior you described.  When I change the *XDIM_ADDMEMBERSET statement to a plain *XDIM_MEMBERSET, the script calculates the expected results.  Furthermore when I remove the *CALC_EACH_PERIOD statement, I get the "invalid member" error upon data submission.

Curiously, I noticed that the inclusion of the *XDIM_NOSCAN statement seems to have no effect.  When I submit a Sales number for October, it still recalculates September AUM regardless of whether or not my script contains an *XDIM_NOSCAN statement.  This is surprising for two reasons: 1) I would think that the *XDIM_NOSCAN statement would prevent September from being recalculated at all; and 2) even if September gets recalculated, I would expect it to produce the incorrect results since the available record set does not include August (which would be a factor in the September calculation).  Perhaps *XDIM_NOSCAN does not work with the TIME dimension, or maybe it is overruled by the *CALC_EACH_PERIOD statement?

As I mentioned, my full script is more complex than what I posted above.  Unfortunately, when I change the *XDIM_ADDMEMBERSET statement in my full script to an *XDIM_MEMBERSET, it no longer produces any calculation results whatsoever.  So I tried adding %TIME_SET% to the scope (i.e., *XDIM_MEMBERSET TIME=PRIOR,%TIME_SET%), with the assumption that %TIME_SET% will contain the list of Time members included in the submitted record set.  (I hope that is true.)  This does make the calculation work again, however I am still getting results that are similar to my initial problem above!

I will need to do some additional testing on my script to isolate where the issue is occurring, however in the meantime if you have any theories on any of my findings above, please let me know!

Cheers,

Alex

cecilia_petersson2
Active Participant
0 Kudos

Hi Alex,

So what is the full scope of your time dimension (I assume you have defined a scope earlier in the script as you use *XDIM_ADDMEMBERSET)? When I first tried to replicate your issue, I had narrowed down the scope to only periods where year=2015, i.e.

*SELECT(%MY_TIME%,"[ID]","TIME","[YEAR]='2015' and [CALC]='N'")

*XDIM_MEMBERSET TIME = %MY_TIME%

*XDIM_ADDMEMBERSET TIME = PRIOR

At that point I couldn't replicate your issue, but when I removed the %MY_TIME% memberset I got the same result as you, can't see the connection. I also tried replacing PRIOR with 2015.SEP but that didn't seem to be the problem. But when I removed the *NO_SCAN instruction, I got the expected result. So I'm not really sure why you're having *NO_SCAN. Is it performance related?

/Cecilia

0 Kudos

Hi Cecilia,

I should have mentioned that this script is included in default logic, so the initial scope is defined by the user-submitted code blocks.

In my example above, the user submits an updated Sales value of $100 in October.  Thus the initial Time scope will be defined as 2015.OCT.  I use the *XDIM_ADDMEMBERSET statement to add the prior period (in this case 2015.SEP) into the record set, so that the prior period AUM can be referenced by the GET statement.

However, by adding September to the scope, the script now wants to execute the logic on September as well.  This is problematic because when trying to calculate AUM for September, it produces a result of $50.  This is because the new record set only includes the $50 Sales value for September, but not the $10,050 AUM value for August.

My hope was that by including the *XDIM_NOSCAN statement, I could prevent the logic from executing on the prior period, while still including the prior period AUM in the available record set (as needed for the current period calculation).

Does that all make sense?

Thanks!

Alex

cecilia_petersson2
Active Participant
0 Kudos

Hi Alex,

I added DataSrc Input to the scope and then it worked as you describe it should, i.e. Aug=10050, Sep=10100, Oct=10200. (I changed ...TIME=PRIOR-%VALUE%... to ...TIME=PRIOR+%VALUE%... in your script; I assume that was a typo.)

Hope that helps!

/Cecilia

0 Kudos

Hi Cecilia,

Thank you for the response!  Unfortunately, adding the Input member to the *XDIM_ADDMEMBERSET statement does not solve the issue for me.  In fact, Input was already included in the scope of my full script, which is much more complex than what I've posted above.  However, even when I test out the simplified script above (with the added Input scope), the behavior remains the same.

When I submit values for all months at once, the calculation works correctly.  But when I subsequently submit a value for October only, it still tries to first recalculate September, producing a result of $50 since it doesn't see the $10,050 in August.  Therefore it still seems to me that the issue is with BPC ignoring the *XDIM_NOSCAN statement, as the script should not even be attempting to recalculate September.  Hopefully that all makes sense, but please let me know if I can clarify anything.

Regarding your question about the +/- of the %VALUE%, I actually do subtract this in my script due to the ACCTYPE and the database signage of the Sales account.

If you can think of anything else to try, please let me know.  Thanks!

Alex