Variable offset on calendar month

[Former Member]

Hello Experts -

I need to display all the months in a year. And then have a YTD column and a Last Year-to-Date column.

My user prompt is the calendar month. How can I use this to display all the months in that year?

I know about variable offset but if the user enters May 2008, then -1 would give me Apr 2008. I need to start from Jan 2008.

Any suggestions any one please?

[Former Member · Accepted solution]

Hello Syed,

Create a customer exit variable under 0calmonth and write the following code .

In the query designer have 0calmonth above the keyfigure structure in the column and restrict with this variable and then have the keyfigure structure.this will give you month wise report.

data: aktyear(4) type n,

aktmon(2) type n,

z_01akt(6) type n,

z_vmakt(6) type n,

when 'test'.

if i_step = 2. "after the popup

loop at i_t_var_range into loc_var_range

where vnam = 'your input variable name'.

aktyear = loc_var_range-low+0(4).

aktmon = loc_var_range-low+4(2).

concatenate aktyear '01' into z_01akt.

concatenate aktyear aktmon into z_vmakt.

clear l_s_range.

l_s_range-low = z_01akt.

l_s_range-high = z_vmakt.

l_s_range-sign = 'I'.

l_s_range-opt = 'BT'.

append l_s_range to e_t_range.

exit.

endloop.

endif.

[Former Member]

I got some ideas. My question was still not answered but I don't think we can do what I need to without a customer exit. So I can live with the solution I have

[Former Member]

Hi Syed,

Using Customer Exit u will get Starting month of the year.

Karthik Egampar... given the correct solution for your problem.

Vijj.

[ravikanth_indurthi]

Hi Syed,

The best way is to restrict Keyfigure with 0CALMONTH and create for each month.

Rgs,

I.R.K

[Former Member]

Hi,

another idea which u can try:

2 variables (start date/enddate) :

Use a second variable for start month and create a value range till your end date without any offset.

Drag and drop your characteristic 0CALCMONTH to free characteristics

In BEx Analyzer u paste in query properties 0CALCMONTH to the KF section by using a horizontal drill down.

The result is that u have for your KF an overall sum and a drill down by month which is in the value range, entered by the user.

Regards

Tobias

[Former Member]

Hi Sayed,

Same kind of reporting requirement i have done for one of my client.

I want 1 clearification:

1. Do you always have to show 12 months ???

What i have understood is that your report layout will be like this.

12 columns of different months of that year,

1 column of YTD figure

1 column of Last year YTD.

In total 14 column.

How to do this:

Dont give the calender month selection in global filter area.

1. Create a restricted figure for your Key figure + calmonth variable in restriction.

2. Create 11 seprate selection figures with offset to this variable -1, -2 ....-11.

3. For YTD figure, create a customer exit variable, create 1 selection figure in column and restrict that to this variable.

4. For Last year YTD do the same as in 3rd point i.e. create another selection figure restrict that to another customer exit variable based on calmonth.

Now in cmod.

Write code to derive the value of YTD & Last year YTD variable from the value of user input variable.

If u stil have question then let me know, this is simple to achieve at query level.

Thanks

Dipika

[Former Member]

Syed,

The best way to achieve this is first create selections in the columns by pulling the calmonth and restrict it a 0calmonth variable and the offset should from -11 to 0, this way you can display 12 months data and for displaying the YTD , create a selection, pull the calmonth and restrict it with ocalmonth variable thru a range and give the offset for these as -11:0, go to properties

under the calculate single value as choose count all values <> 0,

this way you will get the cumulative value of last 12 months,

the best way to achieve the YTD and prior YTD is thru user exits.

*Assign points if helpful****

Peter R

[Former Member]

Peter -

Thanks for the idea.

If the user enters May 2008, then using offset -11:0 will take me into 2007. I need to display all the months of year 2008 even though its in the future. The columns will have 0 but they still would like to see it.

Or I could display Jan - May 2008 when user enters May 2008. But how can I make the offset dynamic?

[Former Member]

Syed ,

you need to have a predefined structure or else there is no point in setting the offset, when you want to display the the last one years along with last 12 months, there should be 12 selections with

ocalmonth and restricted on ocalmon variable with offset values from

-11 to 0 respectively.

I did get, what you meant by dynamic offset.