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

Need to calculate YTD values of a keyfigure in Bex Query

Hi Experts,

I have requirement to calculate YTD values as shown below :

1. User will input Fiscal year in selection screen, suppose 2011

2. Data should be displayed as shown :

Columns: Fiscal year/ period : 001/2011 002/2011 003/2011.......012/2011

Rows : Keyfigure : 100 200 300 ..... 1200

Key figure YTD: 100 300 600 .....

Please suggest how can i get the YTD values currently I have written a customer exit code as below :

CASE i_vnam.

WHEN 'ZYTD_FISCPER'.

IF I_STEP = 2.

LOOP AT I_T_VAR_RANGE INTO LOC_VAR_RANGE WHERE VNAM = '0P_FPER .

CLEAR L_S_RANGE.

L_S_RANGE-LOW0(4) = LOC_VAR_RANGE-LOW0(4).

L_S_RANGE-LOW+4(3) = '001'.

L_S_RANGE-HIGH = LOC_VAR_RANGE-LOW.

L_S_RANGE-SIGN = 'I'.

L_S_RANGE-OPT = 'BT'.

APPEND L_S_RANGE TO E_T_RANGE.

ENDLOOP.

ENDIF.

But it is giving output as :

Columns: Fiscal year/ period : 001/2011 002/2011 003/2011.......012/2011

Rows : Keyfigure : 100 200 300 ..... 1200

Key figure YTD: 100 200 300 .....

Please suggest how can i achieve the desired output

Add a comment
10|10000 characters needed characters exceeded

Related questions

2 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Sep 04, 2011 at 02:44 PM

    Hi Neha,

    In the CMOD code do some changes as below,

    LOOP AT I_T_VAR_RANGE INTO LOC_VAR_RANGE WHERE VNAM = '0P_FPER .

    CLEAR L_S_RANGE.

    L_S_RANGE-LOW0(4) = LOC_VAR_RANGE-LOW0(4).

    L_S_RANGE-LOW+4(3) = '001'.

    L_S_RANGE-SIGN = 'I'.

    L_S_RANGE-OPT = 'EQ'.

    APPEND L_S_RANGE TO E_T_RANGE.

    EXIT.

    ENDLOOP.

    Now you have got the first period of fiscal year entered by user. Now create 12 different RKF's and in each RKF put the lower and higher offset in the restriction using the same variable. e.g. RKF1 will have lower offset as 001 ie. Direct variable and higer offset as VAR11. RKF2 will have 001 and VAR12 as the range. In the similar fashion you can form the 12 different ranges.

    Regards,

    Durgesh.

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Sep 04, 2011 at 07:00 PM

    Hi,

    for cummulation: there is a checkbox in the key figure property. see in the query designer.

    => create a keyfigure.

    => copy this keyfigure and active the checkbox

    => you have two keyfigure. one with the value for each periode. the second with cumulate values.

    Sven

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi Durgesh,

      The problem still persists. when I am giving the offset values as value range as well as when I am restricting for two different values it is not doing aggregation. Is there any other way to do the same.

      Hi Steve,

      Are you refering to Cumulativ/Non cumulative values section when we create keyfigure infoobject. If that is the case, it is already a Cumulative keyfigure and value still are not coming as required. Please suggest if I am missing something in what you suggested.

      Regards,

      Neha

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.