AST Member and Total in final year

[former_member269849]

Hi folks.

Please I have a dimension( A) and I want to display in total of year the last inputed value , because now if  31.12.2015 is empty 2015 is empty How could I achieve that?

thank you 

[former_member186338 · Accepted solution]

You can't achieve it by design of BPC

AST - always YTD, and for YTD the Total is equal to the last base period of the year!

As an alternative you can copy last loaded period to all empty periods till the end of the year.

Vadim

[former_member269849 · Accepted solution]

Thank Vadim for your help , how could I achieve that to copy last loaded period tll the end of year ? Could you give a sample of script logic I can use ?

FZ

[former_member186338 · Accepted solution]

Please look on the MEASURE code using transaction SE38: UJA_MAINTAIN_MEASURE_FORMULA

CLOSINGPERIOD([%TIME%].[LEVEL02])

Help: Custom Measure Formulas - SAP Business Planning and Consolidation, version for SAP NetWeaver - SAP L...

[former_member186338 · Accepted solution]

Script is simple, but what is your model type - YTD or PERIODIC?

[former_member269849 · Accepted solution]

Model type is Periodic.

[former_member269849 · Accepted solution]

I took a look to the code ans i understood it but still dont know how to do this script , and excuse mu ignorance i'm  beginner into bpc.

[former_member186338 · Accepted solution]

For PERIODIC you have to copy only YTD accounts: AST and LEQ

You provide in %TIME_SET% the last period uploaded to copy to the remaining months. Then:

*SELECT(%LASTTID%,[TIMEID],TIME,ID=%TIME_SET%) //get TIMEID of last upload

*SELECT(%Y%,[YEAR],TIME,ID=%TIME_SET%) //get Year of last upload

*SELECT(%TARGET%,[ID],TIME,TIMEID>%LASTTID% AND YEAR=%Y% AND CALC=N)

//%TARGET% will contain remaining periods

*XDIM_MEMBERSET TIME=%TIME_SET%

*WHEN ACCOUNT.ACCTYPE

*IS AST,LEQ

*FOR %T%=%TARGET%

*REC(EXPRESSION=%VALUE%,TIME=%T%)

*NEXT

*ENDWHEN

Vadim

[former_member269849 · Accepted solution]

Thank you  so much Vadim i'm gratful .

FZ

[former_member186338 · Accepted solution]

The same can be also done with RUNALLOCATION:

*SELECT(%LASTTID%,[TIMEID],TIME,ID=%TIME_SET%) //get TIMEID of last upload

*SELECT(%Y%,[YEAR],TIME,ID=%TIME_SET%) //get Year of last upload

*SELECT(%TARGET%,[ID],TIME,TIMEID>%LASTTID% AND YEAR=%Y% AND CALC=N)

*SELECT(%BS%,[ID],ACCOUNT,ACCTYPE=AST,LEQ) //Select all AST and LEQ accounts

*XDIM_MEMBERSET ACCOUNT=%BS% //Scope only AST and LEQ

*RUNALLOCATION

*FACTOR=1

*DIM TIME WHAT=%TIME_SET%;WHERE=%TARGET%

*ENDALLOCATION

Will run faster then WHEN/ENDWHEN

Vadim

[former_member269849 · Accepted solution]

Thank you so much Vadim ,the allocation is working perfectly ,When/EndWhen was giving me an error.

[former_member186338 · Accepted solution]

WHEN/ENDWHEN has to work also! Please show UJKT result!

[former_member269849 · Accepted solution]

UJK_VALIDATION_EXCEPTION:Invalid "select" statement, Same selects  are working for runallocation.

FZ

[former_member186338 · Accepted solution]

Sorry, screenshot of UJKT please!

[former_member269849 · Accepted solution]

Thank you Vadim for your Kind help , Here is it

[former_member186338 · Accepted solution]

For sure it's incorrect:

Instead of:

*SELECT(%LASTTID%,[TIMEID],JOUR=%JOUR_SET%)

Have to be:

*SELECT(%LASTTID%,[TIMEID],JOUR,ID=%JOUR_SET%)

Vadim

[former_member269849 · Accepted solution]

Thank you Vadim, I did  not notice that

[former_member269849 · Accepted solution]

Hi Vadim,

Can we test if this cell contain data or not , for example to repeat the value just for empty cells.

for example we have

01.01.2016  empty  01.02.2016 not empty  01.03.2016 so i do not want to cruch the value of 01.02.2016 i'm wondering if it's possible ?

Thank you.

[former_member186338 · Accepted solution]

"Test" - where?

In general BPC assumes no data equivalent to zero data.. So it's a wrong idea to test for empty!

[former_member269849 · Accepted solution]

Here  in this select

*SELECT(%TARGET%,[ID],TIME,TIMEID>%LASTTID% AND YEAR=%Y% AND CALC=N )

to add Signedata= Empty , i do not want to cruch values that are already there.

[former_member186338 · Accepted solution]

Absolutely not possible: SELECT apply conditions to master data, not on transaction data!

In theory you can create a special property in TIME dimension: LASTLOADED and manually maintain it as Y for the last month with uploaded data. Then the script will not ask user to provide last loaded moth ID, but:

*SELECT(%SOURCE%,[ID],TIME,LASTLOADED=Y)

the use %SOURCE% instead of %TIME_SET%

Vadim

[former_member269849 · Accepted solution]

Thank you so much Vadim for you kind help.

FZ

[former_member269849 · Accepted solution]

Hi Vadim ,

Plz if i want to shcedule this allocation , I changed it from DEFault.LGF to REPARTION.LGF wbut when I try to execute the DM it's giving me an error , time not specified n the DM what should I do ?

regards

[former_member269849 · Accepted solution]

RUN_LOGIC:dimensions :  TIME not assigned in data manager

[former_member186338 · Accepted solution]

You have to provide DM advanced script. The issue is here.

[former_member269849 · Accepted solution]

Here is it

TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,SUSER,%USER%)
TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,SAPPSET,%APPSET%)
TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,SAPP,%APP%)
TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,LOGICFILENAME,REPARTITION.LGF)

I tried with Prompt-> Select ->TIME_DIM but not working

[former_member186338 · Accepted solution]

You have to use SELECTINPUT fot time dimension and pass the result as SELECTION to the script. Please correct the script and show the result.

Also you can fix time member in script logic directly....

[former_member186338 · Accepted solution]

P.S. The idea is simple - you can't use %TIME_SET% variable in script logic if you don' have it in the scope defined by SELECTION. It's a reason for the error.

[former_member269849 · Accepted solution]

PROMPT(SELECTINPUT, %TEMPS_DIM%,,, TEMPS)
TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,SUSER,%USER%)
TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,SAPPSET,%APPSET%)
TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,SAPP,%APP%)
TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,LOGICFILENAME,REPARTITION.LGF)

here is the new advanced script  but what should  I change exactly in the script ?

*SELECT(%LASTTID%,[TIMEID],TIME,ID=%TIME_SET%) //get TIMEID of last upload

*SELECT(%Y%,[YEAR],TIME,ID=%TIME_SET%) //get Year of last upload

*SELECT(%TARGET%,[ID],TIME,TIMEID>%LASTTID% AND YEAR=%Y% AND CALC=N)

//%TARGET% will contain remaining periods

*XDIM_MEMBERSET TIME=%TIME_SET%

*WHEN ACCOUNT.ACCTYPE

*IS AST,LEQ

*FOR %T%=%TARGET%

*REC(EXPRESSION=%VALUE%,TIME=%T%)

*NEXT

*ENDWHEN

[former_member269849 · Accepted solution]

I can not fix it time because it should chage dynamicaly

[former_member186338 · Accepted solution]

In SELECTINPUT line it's better to use %SELECTION% variable. And the TASK SELECTION is missing. You need this task to pass SELECTION as a scope to script logic. Please create new DM package with the chain default formulas - you will see the correct syntax.

[former_member269849 · Accepted solution]

here is the new advanced script

PROMPT(SELECTINPUT,,,,,%TEMPS_DIM%,TEMPS)

TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,SUSER,%USER%)

TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,SAPPSET,%APPSET%)

TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,SAPP,%APP%)

TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,SELECTION,%SELECTION%)

TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,LOGICFILENAME,REPARTITION.LGF)

and the allocation scrpit

*SELECT(%LASTTID%,[TIMEID],TEMPS,ID=%SELECTION%)

*SELECT(%Y%,[YEAR],TEMPS,ID=%SELECTION%)

*SELECT(%BS%,[ID],KPI_CDG,ACCTYPE=AST)

*SELECT(%TARGET%,[ID],TEMPS,TIMEID>%LASTTID% AND YEAR=%Y% AND CALC=N)

*RUNALLOCATION

*XDIM_MEMBERSET KPI_CDG=%BS%

*FACTOR=1

*DIM TEMPS WHAT=%SELECTION%;WHERE=%TARGET%

*ENDALLOCATION

and it's not working and thank you vadim for your time

[former_member186338 · Accepted solution]

IIt's not working because you are correcting one mistake and creating new....

SELECTINPUT syntax is incorrect, please, look on standard script!!!!

in script logic you CAN'T use %SELECTION% you have to use the %TEMPS_SET%

l am currently writing from tablet and can't copy/paste or type a lot!

[former_member269849 · Accepted solution]

PROMPT(SELECTINPUT,,,,"%TEMPS_DIM%")
TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,SUSER,%USER%)
TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,SAPPSET,%APPSET%)
TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,SAPP,%APP%)
TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,SELECTION,%SELECTION%)
TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,LOGICFILENAME,REPARTITION.LGF)

Still not working it's the scrpit that we have by default

and here is my scrpit

*SELECT(%LASTTID%,[TIMEID],TEMPS,ID=%TEMPS_SET%)

*SELECT(%Y%,[YEAR],TEMPS,ID=%TEMPS_SET%)

*SELECT(%BS%,[ID],KPI_CDG,ACCTYPE=AST)

*SELECT(%TARGET%,[ID],TEMPS,TIMEID>%LASTTID% AND YEAR=%Y% AND CALC=N)

*RUNALLOCATION

*XDIM_MEMBERSET KPI_CDG=%BS%

*FACTOR=1

*DIM TEMPS WHAT=%TEMPS_SET%;WHERE=%TARGET%

*ENDALLOCATION

Still have "Temps not assigned in DM

Thank you

regards.

[former_member186338 · Accepted solution]

Not "%TEMPS_DIM%" in SELECTINPUT

but "TEMPS" - real dimension name

or "%TIME_DIM%" - generic name of time type dimension.

Any will work

[former_member269849 · Accepted solution]

Now it's working i have the filter , but i do not want to specify time i want to execute the script for all members and when i selected all members it's gving me the error "" Member** do not exist any idea ?

[former_member186338 · Accepted solution]

The script is not designed to work with all time members. And it's a bad idea in general.

[former_member269849 · Accepted solution]

the problem i have is that the where values are imputed the result is waht i'm expecting but when values are loaded  values aren't correct that's why tr remose the script from the defautlt to schedule a DM ?

what do you suggest ?

[former_member269849 · Accepted solution]

Here is the problem

"Capitaux propores" is the accumulated value of " Capitaux propres moyens " LAST To date , and I have " Capitaux propres moyens" wich accountype is AST the KPI is loaded  and normaly I should have from 2016.05 this value 133685451,05 and not 439452527,15 from 2016.07 .

[former_member186338 · Accepted solution]

Sorry, but absolutely not clear.....

[former_member269849 · Accepted solution]

when the value of KPI is an iput the script is working , but when it's a calculating or loaded value it's not working it's repeating wrong values , do you have any idea why ? is this script just working with inputed value?

[former_member269849 · Accepted solution]

thank you.

[former_member186338 · Accepted solution]

The original script as default.lgf has to work correctly if launched by data load process. Load has to be performed by single month.

Vadim

[former_member269849 · Accepted solution]

that's the problem even with launching data load process it's not working it's giving wrong values

I have  is the accumulated value of a .

till 12.05.2016 it's giving correct values

but when a is empty it's writing the same value that was repeated at first

[former_member269849 · Accepted solution]

and for script i have

*XDIM_MEMBERSET MEASURES = PERIODIC

*WHEN TV_EXTRACTION

*IS a

*REC(EXPRESSION=[MEASURES].[YTD],TV_EXTRACTION = b)

*ENDWHEN

*COMMIT

and

after the script of allocation

is it  the order of scripts ?

thank you

[former_member269849 · Accepted solution]

and then after I have theese values

normally I shoud have 386836,38 repeated from 26.06.2016

what do you think?

[former_member186338 · Accepted solution]

Sorry, but I am completely lost with your explanations and the scripts you have! Also I have no idea how do you launch scripts.

And the idea of the script from your last message is absolutely unclear...

Sorry, may be it's better to open a new discussion and explain everything correctly, step by step?

P.S. My original script was designed for:

1. Single month data load of Balance Sheet accounts (AST,LEQ), data load has to be done in the order of months (01, 02,03....)

2. After loading data to the last month the values of BS accounts will be copied to the remaining months of the year