Home » Fusion Middleware & Colab Suite » Business Intelligence » How Do I Get OBIEE to Analyze a Column to Calculate Start-End Dates?
How Do I Get OBIEE to Analyze a Column to Calculate Start-End Dates? [message #563320] Sun, 12 August 2012 16:59 Go to next message
AMorales
Messages: 1
Registered: August 2012
Junior Member
This one really has me stumped.

I have a report of Projects (Column D) that belong to a series. Each series has its own series ID # (Column A).

Each start date (Column B) should end 28 days later, or if it's within the same series, the next project down on the list would have an End Date a day before the Start Date of the new project. For example, since PROJECT 5 (the first project on the list) doesn't match the series ID # above it, it adds 28 days to its Start Date to get its End Date; but PROJECT 4's ID # matches PROJECT 5's ID # (hence, since they are in the same series) PROJECT 4's End Date is one day before PROJECT 5's Start Date.

Also, notice in the Excel table example below the projects are sorted by ID # first and then by PROJECTS (in descending order).

Basically, I've been using this formula =IF($A1=$A2,B1-1,B2+28) in Excel to figure out what the End Dates would be; and would love to duplicate the same formula in OBIEE but I have no clue how to do it. I can't get it.

A B C D
1 ID # START DATE END DATE PROJECT NAMES
2 01406 31-Jul-2012 28-Aug-2012 PROJECT A 5
3 01406 17-Jul-2012 30-Jul-2012 PROJECT A 4
4 01406 03-Jul-2012 16-Jul-2012 PROJECT A 3
5 01406 19-Jun-2012 02-Jul-2012 PROJECT A 2
6 01406 29-May-2012 18-Jun-2012 PROJECT A 1
7 01658 26-Jun-2012 24-Jul-2012 PROJECT B 2
8 01658 29-May-2012 25-Jun-2012 PROJECT B 1
Re: How Do I Get OBIEE to Analyze a Column to Calculate Start-End Dates? [message #565422 is a reply to message #563320] Tue, 04 September 2012 06:26 Go to previous message
skooman
Messages: 913
Registered: March 2005
Location: Netherlands
Senior Member
Hello,

Firstly, I believe your calculation is a bit too straight forward, you can't add up 28 days hoping that you will end up at the end of the month (you're in trouble for months that have either 31 or 28 days...).

Possibly OBIEE should be able to do this, but referencing another row (the 'previous one' ordered by ID and start date, I believe it should be) is very hard to do in OBIEE. Better to do in the database, using a lag function.

- Sabine
Previous Topic: Patch OBIEE 12561330, 13611078
Next Topic: Need output option as Excel 2007 for Report in BI
Goto Forum:
  


Current Time: Fri Mar 29 07:49:01 CDT 2024