Former Member

# Displaying minimum date value for each row

Hi,

I am trying to create a webi report. The report has the following fields. Data in the database is also in this form.

Vendor No
Name
Changed Date
Status
100 abc 6/24/09 N 100 abc 6/28/13 Y 100 abc 7/2/13 Y 100 abc 9/11/13 N 100 abc 12/1/14 Y

The requirement is to replace the Changed date column with a variable(New Changed Date) that will show the minimum date value for each status. this means that when there are same status in two consecutive rows for a particular vendor number, it will display only one row with the minimum date. For example, here there are two consecutive rows for Y status with different dates. The column will show only the minimum date from the two rows of Y status and when there are no consecutive occurrence of the same status then it will display the date as it is.

The below table will be the required output.

Vendor No
Name
New Changed Date
Status
100 abc 6/24/09 N 100 abc 6/28/13 Y 100 abc 9/11/13 N 100 abc 12/1/14 Y

Please help me with a formula that can give the desired output in Webi. I am using BO XI R3.1

Regards,

Argha

10|10000 characters needed characters exceeded

Dec 24, 2014 at 12:14 AM

You can use the Previous() function to do a comparison of the current value of a dimension with the previous one, but finding a way to get the min of the date of consecutive values is probably not going to be possible.

You would need a way to group the consecutive values together. But since there is the potential for the same Vendor No, Name and Status to exist in non-consecutive rows, it makes it very tricky. I can't think of a way to get this to work.

Here's an example of a formula that gives you a "1" if the previous status is the same as the current.

=If ([status] = Previous([status])) Then "1"

Beyond that, I don't know of a way to make this work the way you want.

Perhaps a consultant out there can assist.

Jb