Retrieving latest value for result set

[#1]

Hi,

I have a report where users can select a date range, and show/hide columns in a table as they wish, so they can see details about accounts, orders etc. One thing they want to see is for the given range who was the last person to own an account. Take the information below for example:

Account Num	Order Num	Date	Owner Name
12345	3456	01/07/2014	Dave Smith
12345	9876	01/05/2015	Bob Jones

So if the user selected 07/2014 to 12/2014 (mm/yyyy) as a range, the returned value would be Dave Smith. However if they extended the range out to be 07/2014 to 06/2015 then the value should be Bob Jones.

I have tried multiple different versions of set analysis to get to this, and I have gotten quite close. However there is one more stumbling block that I need to get over. I have added a hidden column in the table to return the latest date in the range selected, and based the analysis around that. So my current expression is along the lines of:

=MaxString({<[MaxDate]={'$(=Date(Max(Date), 'dd/MM/yyyy'))'}>} AccountOwner)

The results I'm getting depend on what columns are being shown at the time. When I have selected the extended range and have just picked to show Account Num, I get the following result:

Account Num	Owner Name
12345	Dave Smith

This is obviously not correct, as the latest owner for the range is Bob Jones. However in this instance I realise that the MaxString function that I'm using is the culprit here, as when I add in Order Num I get:

Account Num	Order Num	Owner Name
12345	3456	Dave Smith
12345	9876	Bob Jones

This shows that the expression isn't bringing back the last owner for the whole range, but rather the last owner for the account/order combination it can find in the period. Instead I need it to show:

Account Num	Order Num	Owner Name
12345	3456	Bob Jones
12345	9876	Bob Jones

From what I can see I effectively need it to ignore anything lower than the account level when it is retrieving the value, but I don't know how to proceed from here, so was hoping someone could help.

I hope that all makes sense.

[Chanty4u]

=FirstStoredValue([Account num],-[OrderNum])

[sunny_talwar]

More like

FirstSortedValue([Owner Name], -Date)

[sunny_talwar]

Check out the attached (qvw and images)

When Collapsed:

When Not Collapsed:

[#5]

I've put in the function and it appears to be working for a number of records, however for some orders under the same account it is displaying a Null value rather than a name.

I've done some digging around and found that if multiple records have the same sort value (in my case the date) then it causes issues. All the impacted ones have the same date, but there are a handful with this date that ARE showing values as expected.

Is it correct that having the same date may cause this issue? If so is there a way around it?

[sunny_talwar · Accepted solution]

What about this:

FirstSortedValue(DISTINCT [Owner Name], -Date)