DP Macro

[Former Member]

Hi All,

We are trying to use a Macro in our Planning book as per the following logic:

We have specified a Date in ATT105 field and want to read the date, compare it with the bucket end date and if the bucket end date > than the date in ATT105, then consensus FC = 0. Please find below the logic

> DATE TEST MACRO

> Read date from ATT105 : ( 354 Iterations :25.09.2006;01.07.2013 )

> Read date

LAYOUTVARIABLE_SET ( 'MAT_STATUS' ;

MATLOC_EXTRA_C (

5 ;

ACT_PRODUCT ;

ACT_LOCATION ;

ACT_VERSION

)

)

IF

> END DATE < MAT_STATUS

LAYOUTVARIABLE( 'BUCKET_EDATE' )<

LAYOUTVARIABLE( 'MAT_STATUS' )

> Row: Released Consensus Forecast ( Frm 11.07.2011 ) =

Row: Consensus Forecast ( Frm 11.07.2011 )

ELSE

> Row: Released Consensus Forecast ( Frm 11.07.2011 ) =

= 0

ENDIF

Thanks in Advance,

Sandeep

[former_member583013 · Accepted solution]

Hi,

In your example below if condition is incorrect.

IF

> END DATE < MAT_STATUS

LAYOUTVARIABLE( 'BUCKET_EDATE' )<

LAYOUTVARIABLE( 'MAT_STATUS' )

> Row: Released Consensus Forecast ( Frm 11.07.2011 ) =

Row: Consensus Forecast ( Frm 11.07.2011 )

ELSE

> Row: Released Consensus Forecast ( Frm 11.07.2011 ) =

= 0

ENDIF

specially condition LAYOUTVARIABLE( 'BUCKET_EDATE' )<

LAYOUTVARIABLE( 'MAT_STATUS' )

it should be

BUCKET_EDATE(X1) < LAYOUTVARIABLE( 'MAT_STATUS' )

here X1 can be any row or cell or column.

here is syntax:

BUCKET_EDATE( X1 ; ... ; Xn ) returns the latest finish date of the periods given by the cell(s), row(s) and/or column(s) (not areas) X1 through Xn. This date is expressed in whole numbers with reference to the date 01.01.0001. In the attributes of the argument elements, you must specify Period data as the data source.

Hope this will helps you.

Regards,

Sunitha

[Former Member]

Hi Susan,

Thanks for the response. I have changed the ATT105 field value format to YYYYMMDD ( example 20110920). I am using the operator as below and get a short dump

LAYOUTVARIABLE_SET ( 'ZDATE' ;

DATE (ZMAT_ST)

)

ZMAT_ST is value thats in ATT105.

Appreciate any help.

Thanks

Sandeep

[former_member583013]

Hi Sandeep,

If you change the attribute value as bucket date format then no need of DATE function again.

The syntax which i suggested before should work.Check it again.

Regards,

Sunitha

[former_member583013]

Hi Sandeep,

Try the below macro:

> DATE TEST MACRO

> Read date from ATT105 : ( 354 Iterations :25.09.2006;01.07.2013 )

> Read date

LAYOUTVARIABLE_SET ( 'MAT_STATUS' ;

MATLOC_EXTRA_C (

5 ;

ACT_PRODUCT ;

ACT_LOCATION ;

ACT_VERSION

)

)

IF

> END DATE < MAT_STATUS

BUCKET_EDATE < DATE( LAYOUTVARIABLE( 'MAT_STATUS' ) )

> Row: Released Consensus Forecast ( Frm 11.07.2011 ) =

Row: Consensus Forecast ( Frm 11.07.2011 )

ELSE

> Row: Released Consensus Forecast ( Frm 11.07.2011 ) =

= 0

ENDIF

This will work.

Regards,

Sunitha

[Former Member]

Hi Sunita,

I tried that but it didnt work, I am unable to activate the macro.

Thanks,

Sandeep

[m_manimaran]

Hi Sandeep,

Please try this macro.

In the first step our idea is to get the date value from ATT105 and convert that into whole numbers with reference to the date 0001/01/01. Because the BUCKET_EDATE will give the whole number value for the bucket end date. (please make sure you entered the date in the YYYYMMDD format)

Step1:

> DATE TEST MACRO

> Read date from ATT105 : ( 1 Iteration :25.09.2006;25.09.2006 )

> Read date

LAYOUTVARIABLE_SET ( 'MAT_STATUS' ;

DATE(

MATLOC_EXTRA_C (

5 ;

ACT_PRODUCT ;

ACT_LOCATION ;

ACT_VERSION

)

)

)

Step 2:

Get the bucket end date and store it in separate variable: ( 120 Iterations :25.09.2006;25.09.2016 )

Action Box:

LAYOUTVARIABLE_SET ( 'BUCK_EDATE' ;

BUCKET_EDATE(

Row: Consensus Forecast ( Frm 11.07.2011 )

)

)

IF

> END DATE < MAT_STATUS

LAYOUTVAR_VALUE ( 'BUCK_EDATE' )

>

LAYOUTVAR_VALUE ( 'MAT_STATUS' )

Row: Released Consensus Forecast ( Frm 11.07.2011 ) =

Row: Consensus Forecast ( Frm 11.07.2011 )

ELSE

> Row: Released Consensus Forecast ( Frm 11.07.2011 ) =

= 0

ENDIF

Please check this and revert.

One more suggestion to check whether the system reads the data from field is to copy the variable value to some cell.

for example in your case, you may copy the value of MAT_STATUS in a particular cell and see the results. This will help us to nail down the problem further.

Regards,

Manimaran M.

[Former Member]

hi Manimaran,

Thanks for the suggestion, yes it did work but partially. Somehow the condition is not working beyond certain date. say i have date of 20110916 in ATT105, i expect any values in released consensus FC to be zero beyond 20110926. But for some reason the released consensus FC is zero beyonf 20110815. Any suggestions?

Thanks again.

Cheers,

sandeep

[m_manimaran]

Hi Sandeep,

Please check the following things,

1. in the Step , note down the iterations start date

2. Make sure that iterations start date is the date you used in the BUCKET_EDATE statement

3. Make sure in the released consensus FC statement also it is the same.

For Example:

Step2: ( 354 Iterations :25.09.2006;01.07.2013 )

...

...

LAYOUTVARIABLE_SET ( 'BUCK_EDATE' ;

BUCKET_EDATE(

Row: Consensus Forecast ( Frm 25.09.2006 )

....

....

Row: Released Consensus Forecast ( Frm 25.09.2006 ) =

0

If all the above is correct, it should work fine.

Please try and revert.

And in your latest post, you mentioned "say i have date of 20110916 in ATT105, i expect any values in the released consensus FC to be zero beyond 20110926." it should be zero beyond 20110916...right?

is it a type error or you are using 10 days bucket? let me know..

Regards,

Manimaran M

[Former Member]

Hi Manimaran,

Thanks a lot, the macro works. Appreciate your help.

Cheers,

Sandeep Mondkar

[Former Member]

Hi Sunitha,

Thanks for your response. Your suggestion worked partially. As indicated i have defined date in ATT105 field (example 30.9.2011), what i am trying to do is compare this date with Bucket end date of ACT_COLUMN and then have a condition based on ATT105 date greater than bucket end date. I think it is not able to read the value in ATT105 as date. Any suggestions?

Thanks,

Sandeep

[Former Member]

Hi Sandeep,

The format that works with date functions in a macro is this: 20111031. In other words: 2011 = year; 10 = month; 31 = day. Can you populate your date field that way to start? Once you have your date in this format, then you can use the DATE() function to convert it to a numeric date that matches the numeric date used by BUCKET_EDATE() or BUCKET_BDATE().

Example:

Original date: 20111031

use DATE() like this:

DATE(Original date) = 734,442

Compare this number to the BUCKET_EDATE() for October 2011 = 734,442

Does this help?

-Susan