13 Replies Latest reply: Mar 27, 2012 11:20 PM by Yiannis Kasolis RSS

Current Year and previous Year with Month in BEx Query Designer

Nishant Agrawal
Currently Being Moderated

Hello All,

 

I have to create a query in which my columns will have current year and previous year and my rows will have 0calmonth field. Now i need to show data accordingly for example:

I have to show last 12 month data for the current year and previous year respectively. I am not able to apply any CKF and RKF to derive at particular structure. I have created a structure in rows for month calculation with offset but previous year value is not coming. I also tried cell formula but not able to apply for previous year. Any help will be appreciated.

                         currentyear     previous year

01.2011             1000               800(this is 01.2010 data)

02.2011               2000               1200(this is 02.2010 data)

......

.....

12.2011               3000               2000     

 

Thanks & Regards

Nishant

  • Re: Current Year and previous Year with Month in BEx Query Designer
    Yiannis Kasolis
    Currently Being Moderated

    Hello Nishant

    You can create a structure in rows.

    Put twelve formulas. Each one of them will add two lines (2x12 24 lines) which will be the current and the previous year filtered. If you want to have it on two different colums you should have two different year characteristics one for the rows and one for the columns.

    Thank you

    Yiannis

  • Re: Current Year and previous Year with Month in BEx Query Designer
    Jordan Lazarus
    Currently Being Moderated

    Hi Nishant,

    You will have to restrict the current year key figure with offset (month-12 to month) and for the previous year you will have to set the offset like (month-24 to month-12).

    Or else you can easily achieve this by using two Customer Exit variables - One for Current Year and One for Previous Year.

    Let me know if you need the code.

    Regards, Joe

  • Re: Current Year and previous Year with Month in BEx Query Designer
    Farrukh Hanif
    Currently Being Moderated

    I think you have to make 12 different RKF each restricting one month with -1 offset for the calendar month. Make these offset RKF Constant Selection.

    Please let me know if you require more details about how to achieve this pattern.

    Regards,

    Farrukh

  • Re: Current Year and previous Year with Month in BEx Query Designer
    Arun Bala
    Currently Being Moderated

    I don't know why many replies for such hardcoding is really necessary! In X-axis, place 2 elements.  #1 : keep the 'Year' & restrict it with the following [YearVar] [YearVar-1]. In Y-Axis there must be 2 elements, #1 0CALMONTH element & #2 another structure which includes a RKF, which you can create with a "range" option to choose [MonthVar] to [MonVar-12] plus the KF. This must solve your issue. Thanks, Arun Bala

    • Re: Current Year and previous Year with Month in BEx Query Designer
      Jordan Lazarus
      Currently Being Moderated

      Hi Arun,

      Yes, you are right that hardcoding such way is not a good practice but the replies for month and hardcoding of 12 and 24 were given because of what Nishant has requested for

      I have to show last 12 month data for the current year and previous year respectively.

      We all know that year can be directly used for year and previous year values. But not if we need rolling month to month values.

      Regards, Joe

      • Re: Current Year and previous Year with Month in BEx Query Designer
        Nishant Agrawal
        Currently Being Moderated

        Thanks Yiannis, jordan, farrukh and arun for your replies,

        I tried all of your solution but nothing is working.

        I am getting values for current year but not for previous year

        Suppose,
        user has entered 01.2012 as input then my output is

                   current year previous year
        01.2012   1000         0

        if user is inputing 01.2013

        then my required output should be
                    current year previous year

        01.2013    xyz            1000(the value 01.2012 will become previous year).

        But i am not getting any value in previous year

  • Re: Current Year and previous Year with Month in BEx Query Designer
    SANJEEV GUPTA
    Currently Being Moderated

    hi nishant,

                          i think urs question is simple & u just need to do is take ( 0calmonth & 0fiscyear) in rows and take your required key figure in column ( & create two selections in which first selection you will restrict that key figure with 0fiscyear and in second selection you will restrict the key figure by (0fiscyear-1). and you will get the resulta like below.

    rows                                          columns

    0calmonth  0fiscyear                  currentyear     previous year

    01.2011     2011                          1000               800 ( 01.2010)

    02.2011     2011                           2000             1600.( 02.2010)

     

    12.2011      2011                              3000               2400 ( 12.2010).

     

    and you don't need to create 12 rkf or ckf.

     

    regards

    sg2107

Actions