Macro using dynamic fiscal year

[Former Member]

Have any of you ever written a macro that changes the fiscal year on the fly but keeps the same fiscal periods always? For example, I want a macro that copies key figure 1 to key figure 2 for periods 1 - 13 for the next fiscal year. How can I get this to always use periods 1-13 no matter what period I run in but determine the next fiscal year on the fly? If you have done this and could include an example of the macro I would greatly appreciate it.

thanks

Steve

[Former Member · Accepted solution]

Jatin, thank you very much for the quick response. I have attempted to create the macro as you mentioned but I am having some difficulties. Can you take a look and see if you can tell where I went wrong? (I wasn't able to copy / paste the screen shot of the macro so I atttempted to create below).

I am getting an error stating: "The field "FISCAL_YEAR" is unknown, but there is a field with the similar name "CON_FISCAL_YEAR". "CON_FISCAL_YEAR".

macro - Test fiscal year

step - Get fiscal year: (1 Iterations: P 07/2012; P 07/2012)

Aux cell: Hold next fiscal year (P 07/2012) =

Operation: FISCAL_YEAR(ACT_COLUMN) + 1

step - Compare for next fiscal year: (27 iterations: P07/2011; P 07/2013)

Aux cell: Get Loop fiscal year to check ( P 07/2009 ) =

Control statement: IF

Condition: compare for next fiscal year

Operator: (

Aux cell: Get Loop fiscal year to check ( P 07/2009)

Operator: =

Aux cell: Hold Next fiscal year ( P 07/2009)

Operator: )

Row: Operating budget (Frm P 07/2011) =

Row: consensus forecast (Frm P 07/2011)

Endif

[Former Member · Accepted solution]

Hi Steve,

I went through your logic and I believe that below operation is causing the issue:

Operation: FISCAL_YEAR(ACT_COLUMN) + 1

Add space in this operation as below

FISCAL_YEAR(<space>ACT_COLUMN<space>) + 1

Hope this resolves your concern.

Regards,

Jatin

[Former Member]

Jatin, that seems to have fixed my issue. Thank you very much.

[Former Member]

Hi Steve,

This can be achieved by using FISCAL_YEAR and ACT_COLUMN function. One of the possible solution is given below:

1. Create a step with one iteration for current period. In this step get the next fiscal year by using FISCAL_YEAR(ACT_COLUMN) + 1 and store it in an aux cell. This will give you the next year (say 2013). FISCAL_YEAR returns the fiscal year of the period and ACT_COLUMN will point to the current bucket in ongoing iterations.

2. Create a step with greater than 26 iterations (derived from 13 periods for one fiscal year * 2 if we start from start of this year). In this step again fetch the current year as in step 1. Check if current year fetched in this step equals next year if yes then do the computation.

Hope this helps!!

Regards,

Jatin Arora