Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Retrieving latest value for result set

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 NumOrder NumDateOwner Name
12345345601/07/2014Dave Smith
12345987601/05/2015Bob 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 NumOwner Name
12345Dave 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 NumOrder NumOwner Name
123453456Dave Smith
123459876Bob 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 NumOrder NumOwner Name
123453456Bob Jones
123459876Bob 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.

1 Solution

Accepted Solutions
sunny_talwar

What about this:

FirstSortedValue(DISTINCT [Owner Name], -Date)

View solution in original post

5 Replies
Chanty4u
MVP
MVP

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

sunny_talwar

More like

FirstSortedValue([Owner Name], -Date)

sunny_talwar

Check out the attached (qvw and images)

When Collapsed:

Capture.PNG

When Not Collapsed:

Capture.PNG

Not applicable
Author

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

What about this:

FirstSortedValue(DISTINCT [Owner Name], -Date)