BEx designer- Or condition on dates

[Former Member]

Hi All,

I need to extract data using bex query with a condition on start date and end date as .

Start date  From 1/1/2000 to 1/31/2000

OR

End date from 1/1/2000 to 1/31/2000

Which is , all the records that has either startdate or end date as above . If  a record has startdate as 1/21/2000and end date as 2/27/2000  or Null I want it to be captured .can youhelp with suggestions.

Thank you!

[yasemin_kilinc · Accepted solution]

Hi Raja,

Follow this procedure.

Create a selection (lets say KF1)  including your KF, with a filter on Start Date where you filter with the reuqired date range.

Create another selection (lets say KF2) including your KF with a filter on End Date where you filter with the reuqired date range.

Create one more selection ((lets say KF3) including your KF with one filter on Start date where you filter with the reuqired date range and another filter on End Date where you filter with the reuqired date range.

Then simply add a formula such as

KF4 = KF1 + KF2 - KF3.

KF4 is what you require.

Regards

Yasemin...

[Loed · Accepted solution]

Hi Raja,

Follow the suggestion of Yasemin..I lack the subraction of values having both start and end date within the range..

For example: User chose JANUARY 1 - JANUARY 31..

START_DATE          END_DATE          VALUE

1/1                           2/5                       10

12/25                       1/15                      20

1/3                           1/9                       30

RESULT:

KF1               KF2               KF3               KF4

10                  0                   0                   10

0                    20                 0                   20

30                  30                 30                  60 - 30 = 30

Regards,

Loed

[Former Member · Accepted solution]

Hi Yasemin,

Thank you for the response . Data I was refering is only characteristics I dont have any keyfigures.

Eg : Customer data (Name, address, startdate and enddate,.... ) . In some cases my dates will be null and start date and end date may not fall in same month in some cases.

Name   Startdate  Enddate

AAA    1/1/2000     Null

BBB     2/1/2000   1/3/2000

CCC    1/5/2000    3/2/2000

DDD    null             1/8/2000

EEE    Null             Null

I need to select list of customers either started or ended in Jan2000 . In above data example I need to diaplay customers AAA,BBB,CCC and DDD in my report .

If I can use or condition  directly . I can achive my report by  applying condition as Start date between 1/1/2000 and 1/31/2000 or enddate betweeen 1/1/2000 and 1/31/2000.

Thanks,

Raja

[Former Member · Accepted solution]

Thank you for the response . Data I was refering is only characteristics I dont have any keyfigures.

Eg : Customer data (Name, address, startdate and enddate,.... ) . In some cases my dates will be null and start date and end date may not fall in same month in some cases.

Name   Startdate  Enddate

AAA    1/1/2000     Null

BBB     2/1/2000   1/3/2000

CCC    1/5/2000    3/2/2000

DDD    null             1/8/2000

EEE    Null             Null

I need to select list of customers either started or ended in Jan2000 . In above data example I need to diaplay customers AAA,BBB,CCC and DDD in my report .

If I can use or condition directly . I can achive my report by applying condition as Start date between 1/1/2000 and 1/31/2000 or enddate betweeen 1/1/2000 and 1/31/2000.

Thanks,

Raja

[swati_gawade · Accepted solution]

Hi Raja,

If you apply the procedure as explained by Loed and Yasemin on any key figure the characteristics should get restricted automatically.

If you dont want see these KFs in your report then you can simply hide them.

If you dont have any KFs in your cube then used something like count etc.

Hope this helps.

-Swati.

[yasemin_kilinc · Accepted solution]

Hi Raja,

If you don't have any key figures in your cube and not planning to have any on the report, then create a CKF with a formula as 1 and use this KF with the restrictions I mentioned in my previous post. I think that's something like Swati has suggested.

Regards

Yasemin...

[Loed]

Hi,

You may create 2 copies for every KF you have..

For example, you have 3 KFs in your report, let's call them KF_1, KF_2, and KF_3..

You need to create 3 more KFs..So,

KF_1 - filter with START_DATE from 1/1/2000 to 1/31/2000 (or the variable you are using)

KF_1_COPY - filter with END_DATE from 1/1/2000 to 1/31/2000 (or the variable you are using)

KF_2 - filter with START_DATE from 1/1/2000 to 1/31/2000 (or the variable you are using)

KF_2_COPY - filter with END_DATE from 1/1/2000 to 1/31/2000 (or the variable you are using)

KF_3 - filter with START_DATE from 1/1/2000 to 1/31/2000 (or the variable you are using)

KF_3_COPY - filter with END_DATE from 1/1/2000 to 1/31/2000 (or the variable you are using)

HIDE them all..

Create another 3 formulas..

KF_1_FINAL = KF_1 + KF_1_COPY

KF_2_FINAL = KF_2 + KF_2_COPY

KF_3_FINAL = KF_3 + KF_3_COPY

So you still have 3 final keyfigures, the KF_1_FINAL, KF_2_FINAL, and KF_3_FINAL..

Regards,

Loed

[Former Member]

Thank you for the response . Data I was refering is only characteristics I dont have any keyfigures.

Eg : Customer data (Name, address, startdate and enddate,.... ) . In some cases my dates will be null and start date and end date may not fall in same month in some cases.

Name   Startdate  Enddate

AAA    1/1/2000     Null

BBB     2/1/2000   1/3/2000

CCC    1/5/2000    3/2/2000

DDD    null             1/8/2000

EEE    Null             Null

I need to select list of customers either started or ended in Jan2000 . In above data example I need to diaplay customers AAA,BBB,CCC and DDD in my report .

If I can use or condition directly . I can achive my report by applying condition as Start date between 1/1/2000 and 1/31/2000 or enddate betweeen 1/1/2000 and 1/31/2000.

Thanks,

Raja

[Loed]

Hi,

You may try it first then give comments afterwards..

Regards,

Loed

[former_member251664]

using customer exit u can achieve,

[Former Member]

Can you le tme know the process/ steps

[Former Member]

Hi,

here you mentioned, you wanted to filter only Jan 2000 data

Start date  From 1/1/2000 to 1/31/2000

OR

End date from 1/1/2000 to 1/31/2000

but in here, you mentioned both JAN , FEB and null values

 If  a record has startdate as 1/21/2000and end date as 2/27/2000  or Null I want it to be captured 

can you explain what is your requirement ?

[Former Member]

I need the capture all the records that are started or  ended in the same month  . Record might have a start date in JAN  but end date in FEB and vice vera . I need to capture all the records.

[Former Member]

Hi,

Not sure if I understood your requirement correctly, if you need to extract all records including date as NULL , then why do you need condition/filter ?