Using CURRENT MEMBER with dynamic Offset

[dheeraj_gupta]

Hi,

Key FIgures	0CALDAY 01.01.2016	0CALDAY 02.01.2016	0CALDAY 03.01.2016	0CALDAY 04.01.2016	........	0CALDAY 29.02.2016	.....	0CALDAY 31.12.2016
Sales Retail
Sales Retail (Last Year)

I am building a query in which I need to use 0CALDAY in the column and Sales Retail in the rows.

In the rows I want to show Sales Retail for the selected 0CALDAY Range as well as Sales Retail for the same day a year before.

One way to achieve this is by using Current Member functionality and using offsets. The challenge is that the offsets need to be dynamic based on the year in question. This is because no. of days in a year is not constant. Hence for some years I need an offset of 365 days and for others I need  366.

Any ideas how I could achieve the above result using current member or any other functionality..

Regards,

Dheeraj

[stefan_unnebrink]

Hi Dheeraj,

in the current BW 7.40 release it is not possible to have a variable for the offset. But I will keep your usecase in mind, when we will enhance the current member functionality.

If you are able to enhance your data model, you could achieve your report as follows:

Create a characteristic which represents day and month (as far as I know there it is not available in SAP content). Add this characteristic to your infoprovider and derive its value from the day.

In your query you have a drill down by the new characteristic and you need two restricted keyfigures (the first restricted by the current year variable and the second restricted by current year varaible with offset -1).

Regards,

Stefan

[dheeraj_gupta]

Hi Stefan,

Thanks for taking note of my request. The challenge for me to use a day/month characteristic is the following :-

1. I am building planning queries and thus would need the query to work with or without posted

    values in the cube.

2. Adding the current year variable behind the key-figure would mean that I cannot use rolling periods. That is, the same row will not longer represent two years. For example, Sep 2014 to August 2015. If I understand it correct, using exit variable for restriction would result in be being able to use only one year per row.

The solution you proposed is no different than using 0FISCPER3 with 0FISCYEAR, where 0FISCPER3 is defined by calendar days, right ?

The current member functionality allows me to look over years which is perfect for calculation for Key-figures such as :

Stock Turns Days =   Stock for Last 12 months / Sales by Last 12 Months * 30.

By using current member I could look over years ... for instance for turn for Feb 2015. I need to look for Stock and Sales values from Feb 2015 to March 2014.

If I understand it correct. The solution you propose would not allow me to use current member any more?

Regards,

Dheeraj

[stefan_unnebrink]

Hi Dheeraj,    

If you want to compare with the day of the previous year then it is not possible with the current member variable at the moment. If you want to compare month or period with the period of the previous year, then it is possible since the offset is constant.

Notice that a variable for the offset does not solve this issue in case you want to report on several years or the 29-Feb is part of your selection.

There are ideas (which are not in release plans so far) to define certain operators for a current member variable. These could be "begin of period", "end of period", "previous period" where period could be month, year etc where as the current member variable is on day or any other time characteristic. This would give you the flexibility to design the report without much ado.

But we have to solve some semantic issues: e.g. "29-Feb-2014 in previous year" = ? - any suggestions from your side?

Regards,

Stefan

[cornelia_lezoch]

Hi Dheeraj,

I think you will need to create exit-variables that crete the offset.

regards

Cornelia