How to make a Moving total in Query Designer

[Former Member]

Hi,

I make trend analysis over sales.

Besides the actual sales figures, I would like to show a Moving Annual Total (MAT).

I can make this based on a specific period (using an offset of 0 to -11, and so on), but now I want to see the trend over an interval (for instance 12 month).

I am not able to incorporate the interval values into the period variable.

Can anybody help me with this?

Regards,

Erik Henning

[Former Member]

Hi,

I have the same issue here. Can anybody elaborate the steps to derive Moving Annual Total (MAT) in Query Designer?

Thanks and regards.

[Former Member]

Hi Erik,

Welcome to SDN.

Similar to the way you have been using offset with a specific period, you can use offseting with a Variable so use a Period variable and use the interval option to specify the start and end period based on offset.

Hope it helps.

[Former Member]

Thanks for your reaction!

I tried it, but I have a interval period as restriction.

And now I want to use this offset function for each period in the interval, but I don't know how to connect this interval to a period variable with this offset.

Can you help me with that?

Erik

[Former Member]

Make sure your variable is of type Interval, you will be able to set ranges only if the the variable is of type Interval to set the upper and lower limits.

Thanks.

[Former Member]

Tanks again.

I am able to make a time (Fiscal year period) variable of the type interval to set the limits to my query.

I also was able to make a time (Fiscal year period) variable to set my offset to 0 to -11.

The problem is combining those two.

When I combine them, I have to enter the limit values and a value for the offset variable.

When I do that, I get the offset value of the period I entered in all the periods within my limits and that is not what I want. I want this offset period variable to use all the periods of my interval.

Can you tell me how to proceed?

Erik