Monthly Revenue data,YTD, Previous year revenue in Bex query designer

[m_jun]

Hi. I have a monthly profit and loss report where I need to show monthly revenue data ( 12 months) for a current fiscal year. And also YTD, prev year revenue, 2 years back revenue. Can you please explain how it can be achieved in bex query designer?

Example report in the below format-

Particulars

Apr/13

May/13

Jun/13

Jul/13

Aug/13

YTD 13-14(Rs in crores)

% of Rev

2012-13 (Rs in crorres)

% of Rev

2011-12  (Rs in crorres)

% of Rev

Primary Sales

Installs including upgrade

Thanks in advance..

[Former Member · Accepted solution]

Hi

There are many options available to this requirement....

What is the starting month of the year for your client? is it Jan or Apr?

Do you have 0CALMONTH2 or 0FISCPER3 in your cube? This is crtical for this approach..

Assume you have 0CALMONTH2 and 0CALYEAR and year starts with Jan of every year..

1) T get revenues by monthly..

Create 12 RKF for each period starting Jan to December based on 0CALMONTH2 or 0FISCPER3..

Say for JAN, you have create one RKF from revenue with restriction on 0CALMONTH2  = 01 and 0CALYEAR as curent year from SAP exit variable 0CURYEAR..

Similarly create 12 RKF....

2) To get YTD revenue..

Create YTD RKF by restricting revenue JUST only with 0CALYEAR = SAP Customer exit variable 0CURYEAR

3) Create previous year revenues by "OFFSET" function on RKF....

If your year starts on April, then do following.

Assuming you have 0CALYEAR is poluating from 0CALMONTH..say for Jan-2104, 0CALYEAR has 2014, not 2013...

First Create customer exit variable [ say Z_YEAR] on year 0CALYEAR and populate it with your actual year, not current system year...This should be done in I_STEP2 based on system date 'SY-DATUM' like following

IF sydatum+4(2) LE 3.

z_Year = sy_datum(4) - 1 .

ELSE.

z_year = sy_datum(4).

ENDIF.

Say if it's Feb-2014, then actually for you it's 2013..not 2014... but if it's May-2014, then it's 2014.. You can search "How to implement customer exit variable" if you need further details...

1) To get monthly revenue data

Create RKF for Apr by restricting revenue on 0CALMONTH2 as 04 and 0CALYEAR as Z_YEAR.

Key Figures = Revenue

0CALMONTH2 = 04 [for April month, fixed value]

0CALYEAR = Z_YEAR [customer exit variable]

Similar way create for May to Dec periods...now you have 9 RKF.

For Jan, create RKF by restricting 0CALMONTH2 as 01 and 0CALYEAR as Z_YEAR + 1 [offset increase by 1].

Same way for Feb and March.

Now you have 12 RKF  each one for each period..

2) To get YTD [starting April] - create CKF by simply assing above 12 RKF.

3) To get 2012-2013 data [previous year]

Create first RKF by restricting revenue by 0CALMONTH2 as 04 to 12 and 0CALYEAR as Z_YEAR - 1 [Offset decrease by 1]. Now you have Apr - Dec 2012.

Create second RKF by restricting revenue by 0CALMONTH2 as 01 to 03 and 0CALYEAR as Z_YEAR. [now you have Jan-Mar 2013].

Now add these two RKF to get 2012-2013 year date..same way you can do for next previous years..

Please make a key note: in query you have to set to hide key figures if it doesn't have any values - [suppress columns mainly in query properties] to show only months with data...otherwise query output shows all 12 months though you don't have data for furture periods of same year...

Hope this helps you....

Message was edited by: Arun Thangaraj

[m_jun · Accepted solution]

Thanks Arun for the explanation. will check the same and get back to you if required.

[gleo_SRAM]

Hi Mohammed,

All your time variables will be within the selections of the key figure of your columns. So based on the above, you are going to build a structure. In the structure you will have a selection on Fiscal Period to select the relevant month or YTD / Prev YTD range and the key figure.

If you want to give the user the choice of selecting a period then use an input variable - if not you can use some of the standard Customer or SAP exits to select the Current Period or the Previous Period.  You can use Offsets of -1 on these time periods, so where you have Aug/13 above, I would just copy and paste the selection for whatever number of months you need and add in offsets of -1 to get Jul/13, -2 to get Jun/13 etc. etc. etc.

With regards

Gill

[m_jun]

Thanks Gill