Returning Result Object Info at Latest Date

[Former Member]

Okay, I'm stumped again. You've guys helped me before, so I'm hoping you can help me again.

Basically, what I'm trying to do is get a report that provides a status based on the last code entered into our database. Unfortunately my system doesn't have a timestamp, so I can't easily determine the information. Below is my original table (notice the repeating ID numbers).

I created a variable called Max Date to give me the latest date for each ID number --> =Max([Date Code Entered])ForEach([ID]) which works. Next I tried to create a variable that gives me the code at the Max Date (so ID 1358226 I would like to see L-1:10 & TN:98 for the second row), but I'm getting a #MULTIVALUE error. The formula I used was =[Code]ForEach([ID])Where([Date Code Entered]=[vMax Date]). Does anybody have any other suggestions for how I can get the information to pull into my report?  Oh, yea, the formula I'm using for the Code works only if the ID number only has one code entered.

Any ideas you may have will be extremely helpful.

[Former Member · Accepted solution]

Hi,

Create the following variables

MaxDateMeasVar=Max([Date Code Entered]) In ([ID])

FlagDimVar=If([Date Code Entered]=[MaxDateMeasVar];"Show";"Hide")

Bring in ID, Code and Date Code Entered in a table and apply block level filter of FlagDimVar=Show

Hope it helps!

[Former Member · Accepted solution]

Brilliant! I would've never thought of that! I really appreciate it Prashant!