Skip to Content
0

How to calculate two month avg in bex query

Apr 09 at 05:42 AM

47

avatar image

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.

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

4 Answers

Matthias Kadletz
Apr 09 at 08:12 AM
1

Hi Naeem,

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

WIKI Constant Selection

And
Current Member

Regards,
Matthias

Share
10 |10000 characters needed characters left characters exceeded
Matthias Kadletz
Apr 09 at 07:22 AM
0

Hi Naeem,

I recommend you to check the following WIKI page:

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

Regards,
Matthias

Share
10 |10000 characters needed characters left characters exceeded
Koen Hesters Apr 09 at 07:50 AM
0

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


lfyvc.png (7.3 kB)
Show 20 Share
10 |10000 characters needed characters left characters exceeded

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

0

current element

0

Dear Koen,

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

zb2pf.png (39.8 kB)
0

what is the definition of your infoobject ?
cv5er.png (6.2 kB)
0

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

0

Dear Koen,

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

general and replacements path.

thx

0

Hi

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

k40l0.png (7.5 kB)
sqbpy.png (2.5 kB)
0

it is based on 0calmonth.

0

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

0

that is screenshot
untitled2.png (21.0 kB)
0

kf screenshot
untitled3.png (55.7 kB)
0

standard object.
untitled.png (31.3 kB)
0
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

0

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

0

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 ?

0

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

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

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

or just use

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

0
Naeem Iqbal Apr 09 at 07:50 AM
0

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.


gi17s.png (12.1 kB)
iqmtz.png (12.1 kB)
Share
10 |10000 characters needed characters left characters exceeded