Hello experts,
I have the next requirement: Accumulate balance to perfom Consolidation. I have in a BW DSO the data from ECC but I don't have the Ratio 0Balance, so I don´t have the accumulated balance of the accounts, I have the Movements month by month so I need to accumulate from 2010 to %TIME_SET% all the balances from all the accounts.
I have Std dims for Consolidation model:
ACCOUNT, AUDITTRAIL, ENTITY, FLOW, INTERCO, RPTCURRENCY, SCOPE, TIME, VERSION
My logic is get all the years prior to actual, i.e. 2010 to 2018, for 2010 to 2017 variable month will be 1 to 12 but for 2018 months1 will be the selected month. ie 2018.02 variable %months1% should have 1 and 2.
Then accumulate the balance form 2010.01 to 2017.12 + 2018.01 to 2018.02.
I have the next script logic, but not sure whan am I missing, because when I run it in UJKT I got an error:
//Scope *XDIM_MEMBERSET RPTCURRENCY = MXP, USD *XDIM_MEMBERSET FLOW= Opening *XDIM_MEMBERSET INTERCO = I_NONE *XDIM_MEMBERSET VERSION = "Current" //Selection of Months and Years of selection(%TIME_SET%) *SELECT(%YRS%, "[YEAR]", TIME, ID=%TIME_SET%) *SELECT(%YR0%, "[YEAR]", TIME, [YEAR]=%YRS% AND [YEAR]>=2010) *SELECT (%MONTHS1%, "[MONTHNUM2]",TIME, [MONTHNUM2]<=%TIME_SET% AND [MONTHNUM2]>=1) //Scoping all prior Years with complete months *WHEN TIME.YEAR *IS <%YRS% *FOR %MONTS0%= "01","02","03","04","05","06","07","08","09","10","11","12" *XDIM_MEMBERSET TIME= %YRS%.%MONTHS0% *WHEN ACCOUNT *IS "0001060091" *REC(EXPRESSION= %VALUE%, VERSION=Current0) *ENDWHEN *NEXT *ENDWHEN *WHEN TIME.YEAR *IS =%YRS% *FOR %MONTS2%=%MONTHS1% *XDIM_ADDMEMBERSET TIME= %YRS%.%MONTHS2% *WHEN ACCOUNT *IS "0001060091" *REC(EXPRESSION= %VALUE%, VERSION=Current0) *ENDWHEN *NEXT *ENDWHEN
Im am getting an error: UJO_READ:INVALID MEMBERS dimension(TIME)
Any ideas what could be wrong.
Best regards.
Hi!
Strange script...
If you have some month selected in %TIME_SET% then:
*SELECT(%TID%,[TIMEID],TIME,ID=%TIME_SET%) //%TIME_SET%=2018.02 *SELECT(%MNTHS%,[ID],TIME,TIMEID<=%TID% AND CALC=N) //select all previous months including current *WHEN ACCOUNT *IS "0001060091" *REC(EXPRESSION= %VALUE%, VERSION=Current0, TIME=%TIME_SET%) //aggregate to current month *ENDWHEN
Thank you Vadim,
Actually while I was testing, got a similar Code, now will try to do it for multiple %TIME_SET% Selection, for example if I select 2018.02, fill all %TIME_SET%<=2018.02
//Scope *XDIM_MEMBERSET RPTCURRENCY = MXP, USD *XDIM_MEMBERSET FLOW= Opening *XDIM_MEMBERSET INTERCO = I_NONE *XDIM_MEMBERSET VERSION = "Current" //Selection of Months and Years of selection(%TIME_SET%) *SELECT (%FISCYEAR%, ID, TIME, ID<=%TIME_SET% AND [YEAR]>=2010) *FOR %FISCYR%=%FISCYEAR% *XDIM_ADDMEMBERSET TIME= %FISCYR% *WHEN ACCOUNT *IS * *REC(EXPRESSION= %VALUE%, VERSION=Current0, TIME=%TIME_SET%) *ENDWHEN *NEXT<br>
I was testing with one ACC, but its going to be for all ACCTs I think it will take awhile.
Regards.
I do not recommend to use:
ID<=%TIME_SET%
Better to use TIMEID property.
And I do not understand "now will try to do it for multiple %TIME_SET% Selection, for example if I select 2018.02, fill all %TIME_SET%<=2018.02"???
Strange FOR/NEXT... What for???
Thanks,
Il'l change TIME_SET for TIME ID. I'll Include a *for *next to add periods. The last script is to calculate the %TIME_SET% selection in case there is one Selection, but what would happen if the time selection is 2018.01 and 2018.02. The program won't work, I'll add another *for to include the 2nd and 3rd and n selection
Thanks for you comments Vadim, I really appreciate them.
Best regards.
Sorry, but unclear! Please explain again the required logic.
The script can't be used for multiple selection...