Table showing Max value

[QlikBeginner1]

Hello

I have built a simple report. This report is customisable where users can select dimensions and measures to build there own report.

I have an example here which shows a record and compares the status of the record. Currently I am using this expression,

Concat(distinct{$<SourceSystemNamePH={'Acturis BE'}, EffectiveDate -= {''}>}StatusDescription,', ')

However, I do not want to Concat, but show the latest statusdescription of that record. How would you do this?

I have tried this, but does not work.

FirstSortedValue(StatusDescription, -Aggr(<SourceSystemNamePH={'Acturis BE'}>, StatusDescription))

I want to get the max value of the status description where the source system name is equal to acturis BE? what am I doing wrong? it just returns 

[GaryGiles]

Try this:

FirstSortedValue({$<SourceSystemNamePH={'Acturis BE'}>} StatusDescription,-EffectiveDate)

[QlikBeginner1]

Hi @GaryGiles 

This does not seem to work, I wanted to get the latest record Status, is this the set analysis you would use to achieve this? where the source system name = acturis BE and the effective date is not null, the max (latest record) status description for that account?

[QlikBeginner1]

As there may have been multiple records per year, but wanted to compare the max status for that record for two specific source systems. I then use set analysis to see if the two status's match for that record in both source systems. 

[GaryGiles]

You may need to encase the FirstSortedValue in an aggr() function.  What is the fieldname for the value at the beginning of your example 'PC0000065'?

aggr({$<SourceSystemNamePH={'Acturis BE'}>} FirstSortedValue(StatusDescription,-EffectiveDate), FieldName)

Can you post a sample of the data?

[QlikBeginner1]

Please see example data, This is when I was using the Concat expression:

I would like to see 'In force' (Acturis) and 'Cancelled' (Cogen) instead of 'Cancelled, Inforce', because there is only one status for 000008 thats why its showing correct, 

Thanks for your help Giles!  

[QlikBeginner1]

I also tried using 

aggr({$<SourceSystemNamePH={'Acturis BE'}>} FirstSortedValue(StatusDescription,-EffectiveDate), AccountNumber) and come as NULL @GaryGiles 

[QlikBeginner1]

This should be so simple, i cant get it working 😞 I don't see why 

[QlikBeginner1]

I have also tried MaxString({<EffectiveDate= {$(=max(EffectiveDate))}>}StatusDescription)  but this does not work