Skip to Content

How to calculate two month avg in bex query

HI,

I want to create a key figure which should be avg of month which is coming in record and last month. example like .

Calmonth sales_current sales_last_month avg
-------------- --------------- ---------------- -----
06.2017 10,000.00 12,000.00 11,000.00
07.2017 15,000.00 10,000.00 12,500.00
08.2017 12,000.00 15,000.00 13,500.00

Every record is taking sales of that month + sales of last month.
we are not using variable to restric month. we hardcoded 5 year period in report.

need help in it.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • Apr 09 at 08:12 AM

    Hi Naeem,

    if I understood you correctly you should check the Constant Selection.

    WIKI Constant Selection

    And
    Current Member

    Regards,
    Matthias

    Add comment
    10|10000 characters needed characters exceeded

  • Apr 09 at 07:22 AM

    Hi Naeem,

    I recommend you to check the following WIKI page:

    OT-OLAP Exception Aggregation
    -> Formula Exception Aggregation (FAGGR)

    Regards,
    Matthias

    Add comment
    10|10000 characters needed characters exceeded

  • Apr 09 at 07:50 AM

    Hi,

    current sales -> just add Sales KF

    last month sales -> create restricted KF/selection KF -> add Sales KF and add month restrictred by a replacement path variable of the month object -> add offset -1

    avg -> add current sales with last month sales and divide by 2

    grtz

    Koen

    Add comment
    10|10000 characters needed characters exceeded

    • Its not working even in new very simple query . not restricted even.

      we make a test copy of our prd system by upgrading it to 7.40 and I checked same scenario on it.

      its working fine there. so assume its a problem of version.

      if anyone knows how to do in 7.30 please do inform.

      regards

  • Apr 09 at 07:50 AM

    Dear Matthias,

    Thanks for the quick reply.

    if you see below screenshot. Record Number 5 belongs to 10.2017 as month. and there are three key figures.

    one is belong to same month10.2017 but second key figure is belong to 09.2017. one month prior to 10.2017.

    on every record same logic will be apply. for "current sales" column there is no need to restrict as its coming with10.2017.

    I want to know how can I restrict "last month sales" with month 09.2017.

    Add comment
    10|10000 characters needed characters exceeded