Skip to Content
author's profile photo Former Member
Former Member

How to calculate Moving Sum over 3 months

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)?

Add a comment
10|10000 characters needed characters exceeded

Related questions

2 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Oct 15, 2008 at 07:07 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      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

  • Posted on Oct 15, 2008 at 07:31 AM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.