BEx Query Design:Restricted keyfigure with 2 intervallvariables on 0FISCPER

[HeikoGottwald]

Hi colleagues,

I do have the following question:

Scenario:

- calculated keyfigure

- 0FISCPER with intervall variable (input-ready) 1: from-to period,

- 0FISCPER with intervall variable (not input-ready) 2: exclude from selection, from current period-1 to current periode -12

example:

user selects period from 06.2011 to 012.2011 (variable 1)

current period is 010.2011

in the result columns only the interval from 010.2011 to 012.2011 shall be shown (because of exclusion variable 2)

user selection period: 6 7 8 9 10 11 12

current period: 10

Result in Query: 6 7 8 9 10 11 12

columns to be shown because of variable 2 (exclusion): 10 11 12

Question:

Why does the exclusion not work? Even changing the order in the restricted key figure, 1st exclusion 2nd user intervall does not show the period 10 11 12 ...

Thanks for any hint!

Best regards,

Heiko

[Former Member · Accepted solution]

Hi Heiko,

Please proceed with below given approach,

1) In the first variable get the input range of from and to value.

2) Populate the second customer EXIT variable by reading the value of first variable, here you can implement the logic to exclude the range and only assign the range which is required in query.

3) Now you can use these variables in any of the RKF as required and get the individual key figure result over this specific period.

You need to note some of the things before proceeding,

-

---

Do not put any time related restrictions in the global filter so that particula range will be excluded.

-

---

Do not put any time characteristic in drill down, other wise individual key figure value will not be calculated.

Regards,

Durgesh.

[Former Member]

Hi Heiko,

I am not clear on your explanation,but this is what I think you could do on each Restricted Key Figure

1. 0FISCPER - User input

2. 0FISCPER - Not user input but restricted to (current period to current period + 3)

When user selects period 006/2011 to 012/2011 based on user input 0FISCPER.The values you would expect for each Restricted KF are

1. RKF with values ranging from user input 006/2011 to 012/2011

2. RKF with values ranging from 010/2011 to 012/2011

To make sure the values are correct, drill down by 0FISCPER in the report and you would be able to validate the data.

Regards,

JB

[HeikoGottwald]

Hi JB,

thanks for your answer ... I meant excluding 2nd RKF from restriction with values from 010/2011 to 001/2011OFFSET.

Which key figure is stronger? I expect the excluded RKF...

my expected result: periodes 010/2011 to 012.2011.

Again thanks and best Regards,

Heiko

[Former Member]

If both exlcusion and inclusion fetches the same results,its always better to go with inclusion.

Please tell me why there is need for exclusion when you are getting the same results with inclusion and inclusion has better response time than exclusion.

Edited by: JB on Oct 28, 2011 8:55 PM

[former_member199691]

- using first variable you will get data from 006.2011 to 012.2011

- but you need to seee data only for 010.2011 - 012.2011

- for that you are trying to use the exclusion using another variable

• now if you use inlcusion as mentioned by JB, that will not work as both inclusions will work as union and not as intersection.

Approaches to achieve what you want may be

Restrict 0FISCPER on interval with two variables - 1st would be customer exit variable which returns current period and 2nd Variable should read the "To" value of variable entered by user. For user entry, create a dummy RKF which should be used just to read the user input.

(ii) Restrict 0FISCPER on single interval variable of type Customer Exist - In the customer exit, it will read the "To" or high value of user entry (using the dummy KF as explained above) and will return the interval from current period to "To" value

(iii) Restrict 0FISCPER on interval with two variables without dummy KF - 1st variable will be customer exit which returns current period., 2nd variable will be user entry input ready variable in which user just enters the end period.

I hope it helps.

Regards,

Gaurav