Only display row of data with Max Date

building reports webintelligence legacy
1

Hello everyone,

I’m fairly new to BO and BI and I have a report built that displays rows of customer information and their membership dates. So John Doe may have 2, 3, or more rows of data (all the same except the dates of memberships) and I only want to display the row of data that has the max [Membership_End_Date] if that makes sense.

I’ve played with the variables and tried setting up Max(Membership_End_Date]) in Report but there are still multiple rows listed for each member (if applicable).

Thanks for any help.

Jay

duece7 (BOB member since 2016-11-16)

2

I’ve done this by creating a measure variable from the date and using ranking.

=FormatDate([Membership Date];“yyyyMMdd”)

That converts your Membership date to a string - today would be 20161117

Wrap a ToNumber round it to give:

=ToNumber(FormatDate([Membership Date];"yyyyMMdd"))

This now gives 20161117 as an integer.

So, you can create a new measure variable using the number above.

Now you can rank for the top 1 in each Membership ID based on this new Membership Date Number that you’ve created. :slight_smile: