BusinessObjects Board

How to get max activity date for a given Id in a dataset with multiple activity dates per Id

building reports webintelligence
#1

Need to pick off the most recent activity_date from a list of transactions, by Id, from an Excel spreadsheet. (I tried in Excel but I couldn’t get that to work, it’s a semi-complex formula).

Dataset has

  • Id
  • activity dates (maybe 50 - 100 per Id)

Logically = “max(activity date) for each unique Id”. Can I do this in WebI?

Here’s my data …
Activity_Date Customer_Id
8/31/2019 1738523
7/31/2019 1738523
6/30/2019 1738523
5/31/2019 1738523
4/30/2019 1738523
3/31/2019 1738523
2/28/2019 1738523
1/31/2019 1738523
12/31/2018 1738523
11/30/2018 1738523
10/31/2018 1738523
9/30/2018 1738523
8/31/2018 1738523

So I want to pick off the most recent date 8/31/2019 for 1738523

#2

insert id and activity date into WebI table
adjust date to max(activity date) or last(activity date)

just like in a Excel-Pivot-Table

#3

Wow! Nice … thanks!

*Simple!