Balance Sheet postings

[Former Member]

Hi Experts,

I am trying to post a calulations to the balance sheet via script logic.

The first part of the logic is working fine and produces the desired result however the second part of the logic (lookup) do not produce a value

// THIS LOGIC WILL CALCULATE THE ADVANCE TICKET SALES INPUT

*XDIM_MEMBERSET TIME = 2014.12

*XDIM_MEMBERSET CATEGORY=Budget
*XDIM_MEMBERSET ACCOUNT=AC_2016100
*FOR %M%=%TIME_SET%

*WHEN CATEGORY
*IS *
*REC(EXPRESSION=([ACCOUNT].[AC_2016100]/[ACCOUNT].[AC_60501]*365), TIME=TMVL(1,%M%), ACCOUNT=AC_2016100_INP)
*ENDWHEN
*NEXT
*COMMIT

//==================================================================================================

*LOOKUP FINANCE
*DIM INP:ACCOUNT=AC_2016100_INP
*DIM TIME =2015.01
*DIM CATEGORY = Budget

*ENDLOOKUP

*XDIM_MEMBERSET TIME = 2015.01
*XDIM_MEMBERSET CATEGORY=Budget
*XDIM_MEMBERSET ACCOUNT=AC_2016100

*WHEN CATEGORY
*IS *
*REC(EXPRESSION=([ACCOUNT].[AC_60501]*LOOKUP(INP)/365), ACCOUNT=AC_2016100)
*ENDWHEN

The second part should post the balance into the following time period, which it is not doing at present and is there a away that this could be done dynamically i.e. for all future time periods

Thanks in advance

[former_member186338 · Accepted solution]

Sorry, but do you have records in this scope?

*XDIM_MEMBERSET TIME = 2015.01

*XDIM_MEMBERSET CATEGORY=Budget

*XDIM_MEMBERSET ACCOUNT=AC_2016100

Looks like you are scoping destination instead of source.

May be:

...

*XDIM_MEMBERSET ACCOUNT=AC_60501

*WHEN CATEGORY

*IS *

*REC(EXPRESSION=(%VALUE%*LOOKUP(INP)/365), ACCOUNT=AC_2016100)

*ENDWHEN

Vadim

[former_member186338 · Accepted solution]

P.S. Also LOOKUP is not required here (the same model), use member instead:

*XDIM_MEMBERSET TIME = 2015.01

*XDIM_MEMBERSET CATEGORY=Budget

*XDIM_MEMBERSET ACCOUNT=AC_60501

*WHEN CATEGORY

*IS *

*REC(EXPRESSION=(%VALUE%*[ACCOUNT].[AC_2016100_INP]/365), ACCOUNT=AC_2016100)

*ENDWHEN

And NEVER USE COMMIT with WHEN/ENDWHEN - absolutely useless!

Vadim

[Former Member · Accepted solution]

Hi Vadim,

Many thanks for your help.

I have chnaged the logic as you suggested, however the logic is not producing a value.

This is my changed logic.

*XDIM_MEMBERSET TIME = 2015.01
*XDIM_MEMBERSET CATEGORY=Budget
*XDIM_MEMBERSET ACCOUNT=AC_60501

*WHEN CATEGORY
*IS *
*REC(EXPRESSION=(%VALUE%*[ACCOUNT].[AC_2016100_INP]/365), ACCOUNT=AC_2016100)
*ENDWHEN

I have taken the lookup out

Regards

[former_member186338 · Accepted solution]

Test this code in UJKT and post the log!

Do you have data in this scope:

*XDIM_MEMBERSET TIME = 2015.01

*XDIM_MEMBERSET CATEGORY=Budget

*XDIM_MEMBERSET ACCOUNT=AC_60501

Do you have data in this scope:

*XDIM_MEMBERSET TIME = 2015.01

*XDIM_MEMBERSET CATEGORY=Budget

*XDIM_MEMBERSET ACCOUNT=AC_2016100_INP

Vadim

P.S. Test only second part of the code, assuming that first part produced correct values in AC_2016100_INP

[Former Member · Accepted solution]

Hi Vadim,

This now produces a number but only when I change the *XDIM_MEMBERSET ACCOUNT=AC_60501 to a base level number, but not at a hierarchical.

AC_60501 is total sales when does not produce a value but if i change this to a base level member AC_4040100 it produces the correct value.

Regards

[former_member186338 · Accepted solution]

Ups,

"AC_60501 is total sales" - it's a critical info and have to be provided from the very beginning

For sure parent members can't be looped in WHEN/ENDWHEN!

Then you can use other code:

*XDIM_MEMBERSET TIME = 2015.01

*XDIM_MEMBERSET CATEGORY=Budget

*XDIM_MEMBERSET ACCOUNT=AC_2016100_INP //Base!

*WHEN CATEGORY
*IS *
*REC(EXPRESSION=%VALUE%*[ACCOUNT].[AC_60501]/365, ACCOUNT=AC_2016100)
*ENDWHEN

Parent members can be used in expression formula...

Vadim

[Former Member · Accepted solution]

Hi Vadim,

Works a treat many thanks for your help.

Regards