Question Re: QUESTION ABOUT SCRIPT LOGIC TO ACCUMULATE BALANCE in Financial Management Q&A

QUESTION ABOUT SCRIPT LOGIC TO ACCUMULATE BALANCE

former_member182305 — Mon, 29 Jan 2018 18:30:54 GMT

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.

Re: QUESTION ABOUT SCRIPT LOGIC TO ACCUMULATE BALANCE

former_member186338 — Mon, 29 Jan 2018 19:14:51 GMT

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

Re: QUESTION ABOUT SCRIPT LOGIC TO ACCUMULATE BALANCE

former_member182305 — Mon, 29 Jan 2018 19:52:57 GMT

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.

Re: QUESTION ABOUT SCRIPT LOGIC TO ACCUMULATE BALANCE

former_member186338 — Mon, 29 Jan 2018 20:00:08 GMT

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???

Re: QUESTION ABOUT SCRIPT LOGIC TO ACCUMULATE BALANCE

former_member182305 — Mon, 29 Jan 2018 22:30:43 GMT

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.

Re: QUESTION ABOUT SCRIPT LOGIC TO ACCUMULATE BALANCE

former_member186338 — Tue, 30 Jan 2018 06:21:32 GMT

Sorry, but unclear! Please explain again the required logic.

The script can't be used for multiple selection...