BusinessObjects Board

Returning Corresponding Data when using the "Last" aggregate function

I have used sales data for products bought by a particular customer. I have designed the report to show me the “first” invoice date and “last” invoice date that they purchased any particular item. I am trying to figure out how to show the invoice number that corresponds to the “last” invoice date.

If I use the “last” aggregate on this dimension, it returns the highest invoice number associated with that item, but that is not necessarily the last invoice number. (We recycle invoice numbers)

The “last” invoice date formula is simply =Last([Invoice Date])
The invoice number is also simply [Invoice Number]

Could someone please help?

You need to do something along the lines of :
= [Invoice Number] where( [Invoice Date] = max( [Invoice Date] in ( [Customer ID] ) )

I don’t have system access right now, so my syntax is probably a bit off but you should be able to play around with it and get it working. Note, this also assumes there is only one invoice per day per customer. If that’s not accurate, it’s not going to work. The “In” operator may also need to change or may be eliminated based on the aggregation being driven by the fields included in your data block and any other fields required for uniqueness.

first() and last() are based on the order the data is coming from the source.

you have to use min() and max() to find the highest or lowest number/date.