on 01-29-2018 6:30 PM
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
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.
User | Count |
---|---|
15 | |
3 | |
2 | |
1 | |
1 | |
1 | |
1 | |
1 | |
1 | |
1 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.