Skip to Content
Former Member
Feb 25, 2013 at 12:30 PM

Return Single Record Based on MAX of 3 FROMDates


Hi All,

I have a requirement where the users would want to get the 'Energised' and 'De-Energised' Count(Key Figure) for a particular Installation(Characteristic) based on a date range given by the user. For example, please see below sample records. I have three 'FROM' and 'TO' dates combination, each capturing a change in either Supplier or Rate Category or Energised/De-Energised status.

Installation Supplier Rate Cat Supp Valid From SuppValid To Stat Valid From Stat Valid to Rate Cat Valid From Rate Cat Valid To De Energised Count Energised Count 30000969 AAA T031 01.01.1950 12.05.2012 01.02.2012 31.12.9999 01.01.1950 31.12.9999 0 1 30000969 BBB T031 13.05.2012 31.12.9999 01.02.2012 31.12.9999 01.01.1950 05.08.2012 0 1 30000969 BBB T035 13.05.2012 31.12.9999 01.02.2012 15.08.2012 06.08.2012 31.12.9999 0 1 30000969 BBB T035 13.05.2012 31.12.9999 16.08.2012 31.12.9999 06.08.2012 31.12.9999 1 0

Record 2 captures the supplier change from AAA --> BBB which occurs on 12.05.2012 (change in Supp Valid from/Supp Valid to with Record1)

Record 3 captures the change in Rate Category from T031 --> T035 on 05.08.2012 (change in Rate Cat Valid From/ Rate Cat Valid To with Record 2)

Record 4 captures the change in Energised Satus from 1 -->0 on 15.08.2012 (change in Stat Valid From/ Stat Valid To with Record 3)

Now my requirement is that, if the user runs the report from 01.08.2012 - 31.08.2012 I need to show just 1 record for the Installation 30000969. This record should be obtained by comparing all three FROM dates(Supp Valid From,Stat Valid From,Rate Cat Valid From) and get the latest FROM date for the month of August,2012. So in my case, the record with 'Stat Valid From' date of '16.08.2012' should be returned.

30000969 BBB T035 13.05.2012 31.12.9999 16.08.2012 31.12.9999 06.08.2012 31.12.9999 1 0

I have created a Multiprovider on top of a standard DSO and I have set all 3 FROM dates and Installation number as KEY fields in the DSO. Any suggestions to perform the above requirement on BEx is welcome.

Thanks in advance.

PS: I have also attached the sample spreadhseet if needed.