BusinessObjects Board

How to pick off the max(date) record from a list

building reports
#1

I need to pull the record with the max(date) from a list.

I saw other related posts here but they’re all like 10 years old.

I never do fancy stuff in WebI - just variables and filters, nothing like this.

We’ve been on 4.3 for about 8 months, I’m fairly comfortable with it.

We just upgraded to SP-3 but I heard this was unstable.

for for these records I just need to pick off the one with the max(date) (Record 3 here).
Name SSN created
Record 1 - Jane Doe, 123-45-6789, 10-June-2020
Record 2 - Jane Doe, 123-45-6789, 12-Aug-2021
Record 3 - Jane Doe, 123-45-6789, 23-Sept-2022

Thank you.

#2

Start off by creating a variable to capture the Max Date in the entire report…

=Max([Your Date]) In Report

Next create another variable to determine which row Is Max Date…

=If([Your Date] = [Max Date]; 1; 0)

Finally, filter on Is Max Date = 1.

That should be it.

#3

Ah - but are you extracting a single person’s records or a number of people’s records which could have multiple rows?

If the former, then nscheaffer’s process will work. If the latter, then you need to identify the max date for each person. It’s best to use an ID in case you have multiple people with the same name who aren’t the same person. Here you would use =Max([Your date]) in ([Person Reference]) for the first variable.

3 Likes