Displaying minimum date value for each row

[Former Member]

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

[former_member59613 · Accepted solution]

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

[Former Member · Accepted solution]

Thanks Jonathan for your prompt reply.

I understand it is tricky and even i am not sure whether it can be achieved in BO.

Can anyone else help me with this?

Regards,

Argha

[Arjun_KT · Accepted solution]

Hello Argha ,

As mentioned by Jonathan Brown,

You can use =If ([status] = Previous([status])) Then  "1" else "0" as a variable "v_filter "

Then put a Block filter and apply v_filter =0.

This will remove the record which is having repeated Status .

Hope the date will be in Ascending order .

Please try and let me know .

Regards

Arjun KT

[Former Member · Accepted solution]

Hi Arjun,

Thanks a lot for your reply. It worked.

Regards,

Argha