macro to view 1 and 2 year's prior data

[former_member226466]

Hi all,

I have to create a macro at week level so that sales history of 1 and 2 years back can be viewed. The problem is with the years which have 53 weeks in them. e.g. 2009 which are creating an offset while copying from last 2 years. Can anybody help me the logic with? I would appreciate the help.

Thanks.

[former_member187488 · Accepted solution]

Hello,

Not qutie sure about your logic ... are you trying to copy a 53 weeks sales hisitory to a 52 weeks' period?

I think this depends on your request ... In this case, where do you want to put the 53rd history?

Maybe you can use macro function YEAR() to make a judgement ...

What is your macro looks like now? More detailed information is appreciated.

Thanks.

Best Regards,

Ada

[former_member226466 · Accepted solution]

Hi Ada,

thanks a ton for the reply. I want to copy previous years sales history. If previous year has 53 weeks/buckets then it creates problem. from 1 to 52 it copies correctly but the extra 53rd week in previous year gets copied into 1st week of next year. e.g. 53rd week of 2009 gets copied into 1st week of 2011which is not correct. I want to skip 53rd week somehow. so that after 52nd week of 2009 it jumps to 1st week of 2010. I would appreciate the help.

[former_member187488 · Accepted solution]

Hello,

I think you can use macro function WEEK().

If the return value is 53, you can do something special to it.

WEEK( X ) returns the week in a date X that is expressed in whole numbers with reference to the date 0001/01/01. Example: WEEK( DATE( 20001220)) = 51. The argument can either be a number you enter in an Operator/Function dialog box or one of the macro elements cell, row, or column from the planning table or auxiliary table.

Best Regards,

Ada

[former_member226466 · Accepted solution]

Ada,

I am already trying that function but it is not working. Anyways, hanks for ur time.

[former_member187488 · Accepted solution]

By what problem does the macro does not work?

If you still need further help, maybe you can paste your macro here ...

[former_member226466 · Accepted solution]

Hey Ada,

I am trying to insert the screen shot but not able to do that. Do u know any way to paste the code by any chance?

Thank you

[former_member187488 · Accepted solution]

Hi,

You can just copy the macro text as it looks in the macro builder like:

Macro <Macro name>

Step <Step name>

... ...

Best Regards,

Ada

[former_member226466 · Accepted solution]

2 years back 2 cell time forward

copy from 2 years back : ( 157 Iterations : W 35/2009; W 34/2012 )

New action box

VEC_LAYOUTVARIABLE_SET( 'vector' ;

WEEK( BUCKET_BDATE(

Row: Historical Billing Qty. ( Frm W 35/2009 )

)))

Row: time move backward ( Frm W 35/2009 ) =

VEC_LAYOUTVARIABLE( 'vector' )

Row: Historical Billing Qty. 2 Yr. ( Frm W 35/2011 ) =

Row: Historical Billing Qty. ( Frm W 35/2009 )

IF

check for 53 for 2 years back row

WEEK( BUCKET_BDATE(

Row: Historical Billing Qty. 2 Yr. ( Frm W 35/2009 )

) )

>= 53

Row: time move backward ( Frm W 35/2009 ) =

1

Row: Historical Billing Qty. 2 Yr. ( Frm W 35/2009 ) =

TIMESHIFT_BACKWARDS(

Area: [ $Historical Billing Qty. 2 Yr. ( W 35/2009 ) ; $Historical Billing Qty. 2 Yr. ( W 34/2012

Row: Historical Billing Qty. 2 Yr. ( Frm W 35/2009 )

Area: [ $time move backward ( W 35/2009 ) ; $time move backward ( W 34/2012 ) ]

Row: time move backward ( Frm W 35/2009 )

)

ENDIF

IF

check for 53 for Hist Bill row

WEEK( BUCKET_BDATE(

Row: Historical Billing Qty. ( Frm W 35/2009 )

) )

>= 53

Row: time move forward ( Frm W 35/2009 ) =

0

Area: Historical Billing Qty. 2 Yr. ( W 35/2009 ) =

TIMESHIFT_FORWARDS(

Area: [ $Historical Billing Qty. 2 Yr. ( W 35/2009 ) ; $Historical Billing Qty. 2 Yr. ( W 34/2012

)

ENDIF