Bex Query filter records based on key date

[praveenraj_kadumundilraje]

Hi All,

I have the following scenario in Bex Query. In the following data set from the Info Cube, we need to filter records based on single user entered key date such that this date is greater than or equal to the Start Date and less than or equal to the End Date. Is there a way to achieve this. Please give a detailed solution. Thanks in advance.

Start Date     End Date    Company Code  Business

                                                                Entity     Rental Object

02.01.2015	03.01.2013	AH02	3000	A1
02.01.2013	03.01.2012	AH02	3000	A1
02.01.2012	01.01.2008	AH02	3000	A1
31.12.9999	03.01.2015	AH02	3000	A1

[former_member214415]

Hi Praveen,

End date should be greater than start date. There is a issue. Check it.

Thanks,

Swapna jain

[Loed]

Hi,

Create a formula variable for your POSTING DATE variable to make it as KF, let's call it ZPDVAR..

Follow this document:

Convert a Characteristic into a Key Figure (BEx) | SCN

But in the REPLACEMENT PATH tab, choose VARIABLE instead of INFOOBJECT, then type the variable you used for POSTING DATE..

Also in the CURRENCY/UNIT tab, choose DATE instead of NUMBER..

Do the the same for your START DATE and END DATE, let's call them ZSTART and ZEND, respectively..

Follow again  this document:

Convert a Characteristic into a Key Figure (BEx) | SCN

But in the REPLACEMENT PATH tab, choose INFOOBJECT..

Also in the CURRENCY/UNIT tab, choose DATE instead of NUMBER..

So you will have a total of three (3) formula variables..

Create three (3) formulas and insert each of your formula variables on them, let's call them ZFORM_PDVAR, ZFORM_START, and ZFORM_END..

Create another formula, let's call it ZFORM_CHECK, and type this logic:

(ZFORM_PDVAR >= ZFORM_START AND ZFORM_PDVAR <= ZFORM_END)

* 1

+ 0

Finally, create a condition on ZFORM_CHECK so that values having value EQUAL to 1 will only be shown in your report..

Regards,

Loed