How to calculate two month avg in bex query

[malikna2]

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.

[former_member274922]

Hi Naeem,

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

WIKI Constant Selection

And
Current Member

Regards,
Matthias

[malikna2]

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.

[kohesco]

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

[malikna2]

Dear Koen,

I used the same approach. but some how in replacement variable its giving me error.

what should I choose in tab "replacement path" . replace variable with ???

regards

[kohesco]

current element

[malikna2]

Dear Koen,

I am getting this error when using CM ( Current Member ).

[kohesco]

what is the definition of your infoobject ?

[malikna2]

Dear Koen,

Can you please send me screenshot of replacement path variable. for two tabs.

general and replacements path.

thx

[kohesco]

is ZCALM2 only the months ? it is better to use a z*Month based upon reference char 0CALMONTH

[kohesco]

Hi

see sceenshots below, nothing special, but I guess your infoobject is not a correct type, you should use a 0CALMONTH shaped infoobject

[malikna2]

it is based on 0calmonth.

[kohesco]

can you share your screenshots, of the rows/columns and of your KF's -> I used a selection not restricted KF

[malikna2]

standard object.

[kohesco]

It should work, can you check this link

https://wiki.scn.sap.com/wiki/display/BI/Current+Member+Variables

maybe you have something else going one

[kohesco]

or just use

n this selection, there is a restriction on Calendar Year/Month with variable 0CURMEM_CALMONTH with an offset value -1

[malikna2]

that is screenshot

[malikna2]

kf screenshot

[malikna2]

Its mentioning from BW 7.40 . we are using 7.3 right now.

[kohesco]

I'm on 7.4 as well, so it could be, but I don't thinks so, I thought this worked in the past as well.

Which filters do you have defined in general filters ?

[malikna2]

We did not defined any filters. we hard coded the filters.

for calmonth. 5 years data and for one specific company in characterstics restrictions.

[kohesco]

• what do you mean by hardcoded the 5 years ?
• can you try the selection with a simple KF not a cumulated one

[malikna2]

Hardcoded means. in restriction we put calmonth from 01.2013 to 12.2018.

we are not using variable for month selection.

I tried with simple key figure same error.

still trying to find out some way. seems very easy and simple. but become complicated in my scenario.

going to create new query with one kf and then I will try it.

[malikna2]

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

[former_member274922]

Hi Naeem,

I recommend you to check the following WIKI page:

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

Regards,
Matthias