Base Members of Selected TIME Dimension in Script Logic

[Former Member]

Hi,

I think I am having brain freeze here.

How would I get the all base members of a given TIME member in a *XDIM_MEMBERSET statement?

I need to perform an operation for the entire financial year, irrespective of TIME member (in that financial year) the user selects. Assume that only one member is selected when running the package

At the moment, I am using TMVL and doing something like this ... which is quite tedious and inelegant. (I am just using MONTHNUM = 4 for purposes of my trivial example.)

...

*XDIM_MEMBERSET TIME = %TIME_SET%

*WHEN TIME.MONTHNUM

*IS 1

     (Do stuff)

*IS 2

     (Do stuff)

*IS (3

     (Do stuff)

*IS 4

  *REC(FACTOR = 1.1,TIME = TMVL(-3,%TIME_SET%))

  *REC(FACTOR = 1.1,TIME = TMVL(-2,%TIME_SET%))

  *REC(FACTOR = 1.1,TIME = TMVL(-1,%TIME_SET%))

  *REC(FACTOR = 1.1)

  *REC(FACTOR = 1.1,TIME = TMVL(1,%TIME_SET%))

  *REC(FACTOR = 1.1,TIME = TMVL(2,%TIME_SET%))

  *REC(FACTOR = 1.1,TIME = TMVL(3,%TIME_SET%))

  *REC(FACTOR = 1.1,TIME = TMVL(4,%TIME_SET%))

  *REC(FACTOR = 1.1,TIME = TMVL(5,%TIME_SET%))

  *REC(FACTOR = 1.1,TIME = TMVL(6,%TIME_SET%))

  *REC(FACTOR = 1.1,TIME = TMVL(7,%TIME_SET%))

  *REC(FACTOR = 1.1,TIME = TMVL(8,%TIME_SET%))

*IS 5

     (And so on ...You get the idea)

...

*ENDWHEN

Netweaver 10.0 SP4 (CPMBPB 801 SP 0004)

Kind Regards

Nick

[former_member186338 · Accepted solution]

Hi Nick,

Let's assume that in %TIME_SET% you have any single time member. Then:

*SELECT(%Y%,[YEAR],TIME,ID=%TIME_SET%) // %TIME_SET%=2016.05

*SELECT(%FMY%,[ID],TIME,[YEAR]=%Y% AND [PERIOD]="JAN") // %FMY%=2016.01

Then you can use TMVL or other with %FMY%

Vadim

[former_member186338 · Accepted solution]

In your case it can be:

*SELECT(%Y%,[YEAR],TIME,ID=%TIME_SET%) // %TIME_SET%=2016.05

*SELECT(%ALLMY%,[ID],TIME,[YEAR]=%Y% AND [LEVEL]=MONTH) // %ALLMY%=2016.01,2016.02...

*XDIM_MEMBERSET TIME=%ALLMY%

*WHEN TIME.MONTHNUM

*IS 1

     (Do stuff)

*IS 2

     (Do stuff)

*IS (3

     (Do stuff)

*IS 4

  *REC(FACTOR = 1.1,TIME = %Y%.01)

  *REC(FACTOR = 1.1,TIME = %Y%.02)

  *REC(FACTOR = 1.1,TIME = %Y%.03)

...

but I do not understand the logic of your script... please explain what do you want to achieve with some sample data.

Vadim

[Former Member · Accepted solution]

Hi Vadim,

Thanks for your replies. True, perhaps the code is a little bit vague to help with an accurate response

Basically, I have a script that it in three parts. The idea is to have the user select just a single month (normally the month they are working in).

Process:

1. Clear out a specific destination AuditID for the 12 periods in that financial year.

2a. Copy the inputs from a specific source AuditID for the periods up to the selected period. Then perform some extrapolation for the remaining (empty) months of the financial year given the period selected.

3. Copy the workings in (2) above to an new AuditID and Accounts for the 12 periods selected.

Number (2) I am fine with. I need something like TMVL(). Happy there. 🙂

So it's (1) and (3) that is messy. (It works fine, it's just very clunky.) Ideally, I am trying to tidy up that part of code and avoid all the TMVL()'s. So really, all I need to to have the 12 periods in the Memberset selection.

I hope that makes a bit of sense.

Thanks again

Kind Regards

Nick

[former_member186338 · Accepted solution]

Still unable to understand completely the logic of your calculations, but your question is already answered...

For example:

"1. Clear out a specific destination AuditID for the 12 periods in that financial year."

*SELECT(%Y%,[YEAR],TIME,ID=%TIME_SET%) // %TIME_SET%=2016.05

*SELECT(%ALLMY%,[ID],TIME,[YEAR]=%Y% AND [LEVEL]=MONTH) // %ALLMY%=2016.01,2016.02...

*XDIM_MEMBERSET TIME=%ALLMY%

*XDIM_MEMBERSET AUDITTRAIL=SomeMember

*WHEN TIME

*IS *

*REC(EXPRESSION=0)

*ENDWHEN

Vadim

[former_member186338 · Accepted solution]

P.S. To get "periods up to the selected period":

*SELECT(%Y%,[YEAR],TIME,ID=%TIME_SET%) // %TIME_SET%=2016.05

*SELECT(%TID%,[TIMEID],TIME,ID=%TIME_SET%)

*SELECT(%UPTO%,[ID],TIME,[TIMEID]<=%TID% AND [YEAR]=%Y% AND [LEVEL]=MONTH)

//%UPTO%=2016.01,2016.02,2016.03,2016.04,2016.05

[Former Member · Accepted solution]

Thanks, Vadim,

It's a bit of an involved thing, but I think you've got the answer spot on. Will test it and revert.

Much appreciated.

Kind Regards

Nick

[Former Member · Accepted solution]

Awesome Vadim,

I'll definitely be using that!

Appreciate the help. I've learned a a few tricks today.

Kind Regards

Nick

[former_member186338 · Accepted solution]

If some answer is helpful - use helpful mark, not "Like"

[former_member186338 · Accepted solution]

If you want to get more help for this script you have to present a table with months and some sample data: before and after each calculation step...

[jrg_finster3]

Hi Nick,

is this working for you?

*SELECT (%TIMYEAR%,"[YEAR]", TIME, "ID= %TIME_SET%")

*SELECT (%TIMEPER%,"[ID]", TIME, "Year=%TIMYEAR%

*XDIM_MEMBERSET TIME = %TIMEPER%

*WHEN *      //what ever you Need in adition

* IS *

  *REC(FACTOR = 1.1)

*ENDWHEN

Regards Jörg

[Former Member]

Hi Jörg,

This will bring non-base members as well. I'd suggest adding [CALC] =N in the 2nd SELECT.

Regards,

Gersh

[dinesh_veluru]

Dear Nick,

Please try using

*xdim_memberset TIME = BAS(%TIME_SET%)

Thanks,

Dinesh.V

[former_member186338]

Bad idea!

[Former Member]

Hi Dinesh,

Thanks, but that will just bring back a base member. Assuming that I have a single entry from the package run, like 2015.DEC (which is already a base member), I need all the base members for that financial year.

My result should be (in this case) 2015.OCT, 2015.NOV, ..., 2016.SEP. (The financial year begins in Oct)

Ta

Nick