Skip to Content
0
May 28, 2008 at 11:25 PM

Report on 3 month rolling, with calculations

162 Views

Hi,

I need to create a query like this:

ABC | Month | Sales | Stock | Calculation

A | 05.2008 | 300 | 2100 | 2

A | 04.2008 | 400 | 1875 | 1.5

A | 03.2008 | 350 | 3300 | 3

The complication I'm having is for Calculation.

The formula is:

Calculation = Stock of the month / (Sales of the 3 previous months).

In the example:

For 05.2008, Calculation = 2. Formula = 2100/(300400350)

For 04.2008, Calculation = 1.5. Formula = 1575/(400350500). Where 500 is the value of sales for 02.2008 (won't be shown in the report)

For 03.2008, Calculation = 2. Formula = 2100/(350500450) Where 500 is the value of sales for 02.2008 and 450 is the value of sales for 01.2008 (won't be shown in the report).

05.2008 is actual month. So next month, should go from 06.2008 to 04.2008.

How can I make this calculations dynamic? I've been able to do them using structures and defining the formula of each month using variables (different for each month). I cannot find out how to get the previous 3 months sales for each month to divide.

Thanks in advance!!

Regards,

Diego