Problem in scriptlogic allocation according to reference data of last year

[Former Member]

Hi BPC gurus,

I got a problem when trying to do an allocation with a scriptlogic file. We have NW version 7.0 patch 07. I created a new file which is included in the default one. I would like to allocate a plan value for several materials on a year base to the months according to reference data from the previous year. This works fine - but only as long as I enter just one value for one material in my input schedule and save the data. If I enter a value for more than one material, the value is allocated not only to the months but also between the materials, so that the sum of the months for one material is much smaller than the value I entered on a year base.

Does anybody have an idea? Here is my coding:

*RUNALLOCATION

*FACTOR=USING/TOTAL

*DIM KONTO WHAT=9999999; WHERE=<<<;USING=<<<;TOTAL=<<<;

*DIM CATEGORY WHAT=PLAN; WHERE=<<<; USING=ACTUAL;TOTAL=<<<;

*DIM P_DATASRC WHAT=MANUAL; WHERE=<<<;USING=UPLOAD;TOTAL=<<<;

*DIM TIME WHAT=2010_INPUT; WHERE=BAS(2010.TOTAL);USING=BAS(2009.TOTAL);TOTAL=<<<;

*ENDALLOCATION

Thanks a lot in advance for your reply!

Best regards,

Ulrike

[Former Member]

Hi Ethan,

thanks a lot for your reply. We did it meanwhile in a similar way. We used lookup function to refer to the reference data, but it is good to know how we can do it without the lookup. Just for the other guys reading the blog, here is our coding:

*LOOKUP Vertrieb

*DIM MEASURES="PERIODIC"

*DIM CATEGORY="ACTUAL"

*DIM P_DATASRC="UPLOAD"

*DIM PREVYEAR:TIME="2009.TOTAL"

*DIM PREVJAN:TIME="2009.JAN"

*DIM PREVFEB:TIME="2009.FEB"

...

*ENDLOOKUP

*WHEN TIME

*IS "2010_INPUT"

*WHEN CATEGORY

*IS "PLAN"

*WHEN P_DATASRC

*IS "MANUAL"

*REC(FACTOR=LOOKUP(PREVJAN)/LOOKUP(PREVYEAR),TIME="2010.JAN")

*REC(FACTOR=LOOKUP(PREVFEB)/LOOKUP(PREVYEAR),TIME="2010.FEB")

...

*ENDWHEN

*ENDWHEN

*ENDWHEN

[Former Member]

We are really BPC newbies, but as we didn't get an answer so far, we tried to write script logic to make a simple formula for the allocation. The coding can be found below

[TIME].[#2010.JAN]=[TIME].[2010_INPUT]*[TIME].[2009.JAN]/[TIME].[2009.TOTAL]

[TIME].[#2010.FEB]=[TIME].[2010_INPUT]*[TIME].[2009.FEB]/[TIME].[2009.TOTAL]

[TIME].[#2010.MAR]=[TIME].[2010_INPUT]*[TIME].[2009.MAR]/[TIME].[2009.TOTAL]

[TIME].[#2010.APR]=[TIME].[2010_INPUT]*[TIME].[2009.APR]/[TIME].[2009.TOTAL]

[TIME].[#2010.MAY]=[TIME].[2010_INPUT]*[TIME].[2009.MAY]/[TIME].[2009.TOTAL]

[TIME].[#2010.JUN]=[TIME].[2010_INPUT]*[TIME].[2009.JUN]/[TIME].[2009.TOTAL]

[TIME].[#2010.JUL]=[TIME].[2010_INPUT]*[TIME].[2009.JUL]/[TIME].[2009.TOTAL]

[TIME].[#2010.AUG]=[TIME].[2010_INPUT]*[TIME].[2009.AUG]/[TIME].[2009.TOTAL]

[TIME].[#2010.SEP]=[TIME].[2010_INPUT]*[TIME].[2009.SEP]/[TIME].[2009.TOTAL]

[TIME].[#2010.OCT]=[TIME].[2010_INPUT]*[TIME].[2009.OCT]/[TIME].[2009.TOTAL]

[TIME].[#2010.NOV]=[TIME].[2010_INPUT]*[TIME].[2009.NOV]/[TIME].[2009.TOTAL]

[TIME].[#2010.DEC]=[TIME].[2010_INPUT]*[TIME].[2009.DEC]/[TIME].[2009.TOTAL]

Unfortunately the data from 2010 is on CATEGORY PLAN and P_DATASRC MANUAL while reference data from 2009 is on CATEGORY ACTUAL and P_DATASRC UPLOAD. How can we refer to several dimensions in the formula? Please assist. Thanks a lot

Best regards,

Ulrike

[esjewett]

Hi Ulrike,

Try (line breaks for readability):

[TIME].[2010.JAN] = ([TIME].[2010_INPUT],[CATEGORY].[PLAN],[DATASRC].[MANUAL])
*([TIME].[2009.JAN],[CATEGORY].[ACTUAL],[DATASRC].[UPLOAD])/([TIME].[2009.TOTAL],
[CATEGORY].[ACTUAL],[DATASRC].[UPLOAD])

Or, possibly faster:

*XDIM_MEMBERSET CATEGORY = PLAN
*XDIM_MEMBERSET DATASRC = MANUAL

*WHEN TIME
*IS 2010.JAN
*REC(EXPRESSION=([TIME].[2010_INPUT],[CATEGORY].[PLAN],[DATASRC].[MANUAL])
*([TIME].[2009.JAN],[CATEGORY].[ACTUAL],[DATASRC].[UPLOAD])/([TIME].[2009.TOTAL],
[CATEGORY].[ACTUAL],[DATASRC].[UPLOAD]))
*ENDWHEN

Ethan