Macro to copy values from one week to other.

[Former Member]

Folks,

Here is the scenario:

We have a SNP planning book and as part of weekly SNP planning cycle, we are backing up a time series KF every week in to a back up cube before the version copy job and reloading that KF from the cube after the version copy.

Each week we will have a new week at the end of the horizon.

This new week will be blank as the new week's data wont be available in the backed up cube.

What would be macro logic to copy values from the new week-1 to new week. This macro needs to be run as part of the planning cycle right after the reload from the cube

e.g. in this week the last week of the planning horizon is 06.02.2012 u2013 this is shown in the planning book.

In the next weekly planning cycle the last week shown in the planning book will be 13.02.2012 u2013 this will be blank.

The requirement here is to copy the entry of week 06.02.2012 into 13.02.2012.

I wrote this simple macro to achieve this. But, i am not sure if the weeks in the macro steps will roll over when we get in to the next week!!

week shift Test

week shift : ( 84 Iterations :Initial;13.02.2012 )

Row: Target DOS next planning run ( Frm 13.02.2012 ) =

Row: Target DOS next planning run ( Frm 06.02.2012 )

Any inputs?

[Former Member · Accepted solution]

Hi Shankar,

As per my understanding you need to have last week (n weeks)of planning horizon data to be copied to last week(n+1)

nth week data copied to (n+1)th week.

In this case you need to perform only one iteration

i.e for nth week when you have entered on (n+1)th week

in the macro step you need to select column

06..02.2012= wk 6.2012 and 13.20.2012=wk7.2012

so it will be for iteration 1(wk 07.2012)

column(wk 07.2012)=

column(w 06. .2012)

By this all the key figures value from wk06.2012 will get copied to wk (07.2012)

Please let me know if this works out.

Regards,

Chetana

[Former Member]

The macro seems to work!!! and the weeks seem to roll over every week.

[Former Member]

Hi,

Pls follow as below.

Copy to Aux KF : ( 84 Iterations :Initial;13.02.2012 )

Row: Auxillary KF row ( Frm 06.02.2012 ) =

Row: Target DOS next planning run ( Frm 06.02.2012 )

Copy back to TS KF: ( 84 Iterations :Initial;13.02.2012 )

Row: Target DOS next planning run ( Frm 13.02.2012 ) =

Row: Auxillary KF row (Frm 06.02.2012)

Pls note the use of an auxillary KF to temporarily store your values, which will then be copied over into your target periods.

Hope this helps.

Thanks

Mani Suresh

[Former Member]

Hi Suresh,

Instead of using a Auxilary KF, can i not just write a macro like below?

Week shift: ( 84 Iterations :Initial;13.02.2012 )

Row: Target DOS next planning run ( Frm 13.02.2012 ) =

Row: Target DOS next planning run (Frm 06.02.2012)

I tried this and this seems to be working (copying from 06.02.2012 to 13.02.2010) for now. But my concern is whether the weeks mentioned in the macro will roll over to the next week when the planning book will have a new week

eg., the week after will have 20.02.2012 as the last week in the book. My question here is - will the above defined macro rolls over as below and copy value from 13.02.2012 to 20.02.2012? I will have to wait until coming monday to check if the weeks really did roll over!!!

Week shift: ( 84 Iterations :Initial;20.02.2012 )

Row: Target DOS next planning run ( Frm 20.02.2012 ) =

Row: Target DOS next planning run (Frm 13.02.2012)

[Former Member]

Hi,

Yes it will..

Thanks

Mani Suresh