How to calculate Moving Sum over 3 months

[Former Member]

Hi,

How can we calulate moving sum from a cube with historical transaction data? For example we want to generate a report in Bex Analyzer with the sum over 3 months as follows:

[Year] [Month] [Issue qty] [Issue qty last 3 months]

2008 Jan 10 10

2008 Feb 19 29

2008 Mar 6 35

2008 Apr 23 48

2008 May 15 44

2008 Jun 17 55

2008 Jul 4 36

So the column [Issue qty last 3 months] for the month of June must contain the total qty issued over June, May and April.

The aggregation for the monthly Issue qty will of course automatically be done by BEx Analyzer, but as far as I can see the aggregation over the last 3 months is not possible in Query Designer or BEx Analyzer (without using "Convert to formula").

Does anyone know how to calculate a moving sum like this (in the Front-End or the Back-End)?

[Former Member · Accepted solution]

Hi Jepp,

As you are trying to show 3 months(Apr, May, June) sum againest one month(June) its not possible in normal way,

You can try using sell definitions. But performance may not be that good.

Please check: [Defining Exception Cells|http://help.sap.com/saphelp_nw04/Helpdata/EN/cb/89fa3a0376a51fe10000000a114084/frameset.htm]

Or Add one new keyfigure for 3months sum and populate at infoprovider level based on your logic.

Hope it Helps

Srini

[Former Member · Accepted solution]

>

> You can try using sell definitions. But performance may not be that good.
> Please check: [Defining Exception Cells|http://help.sap.com/saphelp_nw04/Helpdata/EN/cb/89fa3a0376a51fe10000000a114084/frameset.htm]
> 
> 
> Or Add one new keyfigure for 3months sum and populate at infoprovider level based on your logic.
> 

Hi Srini,

I do not have a lot of experience with Exception Cells, but if I understand correctly then this solution would not be very flexible, right?

A new key figure at the cube level would be preferred, but I have no clue how I would populate this additional key figure and what kind of aggregation to use (if any) for the info object.

Is there an example you can refer me to, or is there maybe a good example in the standard BI Content?

[Former Member · Accepted solution]

Hi Jepp,

Adding a new keyfigure also wont solve your problem. As last 3 months changing so dynamically.

Instead of displaying vertically, try hoirzontally, so you can achieve this, try once...

Month -
Last--
Month--Last
Month---Last
Jan--
3months
Feb
3month---Mar--
3Months

Create restricted keyfigues for Jan, Last 3 Months - Jan(restrict from last 2month to Jan) ....etc.

You can use Offset to achieve same. Make User Input as starting Month or current Month and out of which derive accordingly using offset.

If you make it vertically, 2 time dimensions are coming, for horizontal only one time dimension comes. and also do not give any drill down to cal month, cal quarter or year. You just create restricted keyfigures for all.

If its not clear, please inform to me..!!

Srini

[Dont forget to close the call by assigning poings.... - Food for Points: Make a Difference through Community Contribution!|https://www.sdn.sap.com/irj/sdn/index?rid=/webcontent/uuid/007928c5-c4ef-2a10-d9a3-8109ae621a82]

[Former Member · Accepted solution]

Hi Srini,

I understand what you are saying, but it is not really what we are looking for. The query will be more or less hard-coded, so next thing user wants is 6-month or 12-month moving average etc, and we will have to hardcode another query again. Also moving sum should continue over year end/start, and user should be able to select the period to be reported over, for example 3 years (so not just 1-year periods).

Currently we are looking into creating a history cube (or DSO), which we can then use to store (summarized) results on a monthly basis.

Thanks anyway for your help, helped me discover Exception Cells.

[Former Member · Accepted solution]

Hey Guys,

I understand the concept here, but we have a little different requirement in the same lines.

In our case the user wants to enter the period for which the moving sum should be calculated. Is there any way I can achieve this in

1. Query Level

2. Infospoke level for transporting as a flat file.

Query level, if some one can let me know how to go ahead with any formula's, user exits, replacement path etc.

At the Infospoke also for retrieving the data to an excel spreadsheet, users want to display data with Moving sum like they want in Query.

Is this possible in BW?

Regards

[former_member181964]

We have this requirement, and we did like below.

Create two customet exit variable called FMYEAR and LMYEAR and wrirte code in CMOD.

Just restrict the Keyfifure (in formula) using LMYEAR.

Eg: you want to see April + May + June data.

Just create formulas with above restriction for April the formula is like below.

• First Month Of Current Fiscal year comment*

WHEN 'FMYEAR'.

CALL FUNCTION 'DATE_TO_PERIOD_CONVERT'

EXPORTING

i_date = sy-datum

• I_MONMIT = 00

i_periv = 'V3'

IMPORTING

e_buper = zbuper

e_gjahr = zbdatj.

CLEAR: l_s_range.

l_s_range-low+4(2) = '04'.

l_s_range-low+0(4) = zbdatj.

l_s_range-sign = 'I'.

l_s_range-opt = 'EQ'.

APPEND l_s_range TO e_t_range.

-

---

• Last Month of Current Fiscal year*

WHEN 'LMYEAR'.

CALL FUNCTION 'DATE_TO_PERIOD_CONVERT'

EXPORTING

i_date = sy-datum

• I_MONMIT = 00

i_periv = 'V3'

IMPORTING

e_buper = zbuper

e_gjahr = zbdatj.

CLEAR: l_s_range.

l_s_range-low+4(2) = '03'.

l_s_range-low+0(4) = zbdatj + 1.

l_s_range-sign = 'I'.

l_s_range-opt = 'EQ'.

APPEND l_s_range TO e_t_range.

Restrict CALMONTH with LMYEAR-11

For May

Restrict CALMONTH with LMYEAR-10

For June

Restrict CALMONTH with LMYEAR-9

finally add all there three formulas,you get sum of 3 months.