Get rolling 12 months for a variable number of months

[Former Member]

Hi Forum,

There's the following problem I hope to get solved with you:

The users can define a range of an 0calmonth (referenced).

So, each month in this range is provided in columns, one keyfigure is selected in rows.

The result should be an average value of the last 12 months for each month.

We had to do the same for just one year, so we could use static selections.

But is there a way to do this dynamically?

I'm looking forward for any idea.

Many thanks in advance,

Tobias

[Former Member · Accepted solution]

Hi Tobias,

I think, you can use a restricted key figure, with a restriction on calendar month.

The restriction on calendar month should be of value range type with formula variable and offset.

The formula variable should be of replacement type and should be replaced by info object key (from value).

Place a restriction as FV-12 to FV

Use calmonth in the column.

In the rows use this restricted key figure.

So at each cell it will look up to the column value and derive the formula variable value from it.

So say calmonth is 10/2008 in the column in the key figure restriction it would get restricted by 10/2007 to 10/2008.

The key figure value in the calculation tab can be set to calculate as average value.

Hope this helps.

Regards,

Sunmit.

[Former Member · Accepted solution]

Hi Sunmit,

sorry for that great delay.

Thanks for this great post.

Unfortunately, I don't get it work, yet.

I've created an restricted key figure.

Therein is a restriction on calyearmonth and the key figure.

The calyearmonth is restricted by a characteristic variable (range with offset char.var.-11 to char.var.)that contains a replacement path of a formula variable key.

The formula variabel itself has a replacement path "InfoObject" and key selected.

I can't select a formula variable in the calyearmonth restriction.

Unfortunately an error message is shown in the characteristic variable regarding the selected replacement type.

Can you please give me a hint again?

[Former Member]

HI ,

There r 2 Solutions which i think of :

1> Using Virtual Characteristic ZCALMONTH

a)

Create a Virtual Characteristics for ZCALMONTH

and pass the "From Value" entered via the Variable ZCAL1 of 0CALMONTH, to the Vir Char Exit where in the 0CALMONTH would be checked , if 0CALMONTH < ZCAL1 then change the 0CALMONTH Value to "From Value"

For Example ZCAL1 = 05.2005 - 10.2005

So if 0CALMONTH in the Exit comes 06.2004, the value is changed to 05.2005

b) Create a Query in which u use 0CALMONTH in the Filter with Variable ZCAL1 and teh from Value is Offset by -12 months

and in the column use ZCALMONTH

and the KF divide it my 12 - make it cumulative

2> Fix the columns.

For Example Fix the columns for the 12 months of a year, and use restrictions on 0CALMONTH to arrive at the totals and then average them

[Former Member]

Hi,

thanks a lot for your great ideas.

I've selected the fixed columns, that seems to be acceptable for our customer.

May be we get back to the RKF and replacement path later.

Regards,

Tobias

[Former Member]

A range for 0CALMONTH is somewhat trouble some I believe.

How many months does the range can have?

If I understood properly, I think it is for 12 months only I believe.

Again will these 12 months be in a sequence?

Well, to make this dynamic you can go for a customer exit, but you have to create 12 variables to pass on the required range of 0CALMONTH which will rstrict the key figures i.e it will get the sum of 12 months as you said. All the 12 columns should be hided. Now in a new formula(12 columns) you can use a formula diving by 12 for each hidden column. This is raw idea. I hope you can frame it as you require....

[Former Member]

Hi,

Thanks for that quick response.

The range is not limited to specific number, unfortunately.

The range itself is one sequence and the number of rolling months is fix (12).

Only the range can be selected without limitations.

Unfortunately, I've no experience regarding the customer exit.

Is it your idea to creat an own variable for each month in the range (number is unknown) to

get the sum of its 12 months?´

Is there another way by using ther rolling average function in Query Designer or WAD?

I's looking for an option to set a parameter for 12 rolling months in Query Designer.

Edited by: Tobias Vogt on Jan 19, 2009 5:06 PM