Comparing 3 list of dates

[Former Member]

Hi,

I have created a WebI report (SAP Business Objects BI Platform 4.0 Client Tools (version 14.0.2.364)), on top of a BEx query which was built on top of one multiprovider in SAP BW. Within the report, I am pulling 3 different dates (Date1, Date2, and Date3). Date2 and Date3 are from two separate InfoCubes with Date1 in BOTH of the InfoCubes. Therefore, I was planning to use Date1 as the key identifier to tie Date2 and Date3 together.

Essentially, I want to do the following:

• Find the Maxdate of Date1 for each corresponding Date2 fields in InfoCube1. (MaxDate1 =Max(Date1) where InfoCube = InfoCube1)

• Find the list of Date3 where the MaxDate1 from InfoCube1 is equal to the Date1 in InfoCube2.

(InfoCube2Date1 = Date1 where InfoCube = InfoCube2)

(Date3List = Date3 where MaxDate1 = InfoCube2Date1)

• Find the Max Date3 within Date3List where Date3 < Date2

(MaxDate3 = Max(Date3List) where Date3List < Date2)

The steps above did not work at all. Ultimately, I need to find the max Date3 where Date3 is less than Date2.

Can anyone help?

-Janice

[Former Member]

Hi, can anyone help? Does the question make sense?

[Former Member]

Hi JANACN

There are lot of problems involved with max(date) variable created at the webi report level.Because if you write max(Date1) and place that object in the report the date will checked for row wise. So u will get all dates even though u use max function.

So normally create these max() function variables at the universe level. But as u are using Bex Query so try create all the max variables in Bex level it self and then drag it to the report level.

For Your Requirement Create Max(date1) in infocude and then at the report level just create one more as below

= date3 where (infocube2.date1 = maxdate1 and date3< date2)

Regards

Shyam