topic Table showing Max value in App Development

Table showing Max value

QlikBeginner1 — Sat, 16 Nov 2024 17:23:50 GMT

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

Re: Table showing Max value

GaryGiles — Fri, 12 Mar 2021 12:22:49 GMT

Try this:

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

Re: Table showing Max value

QlikBeginner1 — Fri, 12 Mar 2021 13:19:28 GMT

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?

Re: Table showing Max value

QlikBeginner1 — Fri, 12 Mar 2021 13:20:59 GMT

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.

Re: Table showing Max value

GaryGiles — Fri, 12 Mar 2021 13:32:18 GMT

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?

Re: Table showing Max value

QlikBeginner1 — Fri, 12 Mar 2021 14:00:44 GMT

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!

Re: Table showing Max value

QlikBeginner1 — Fri, 12 Mar 2021 14:02:19 GMT

I also tried using

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

Re: Table showing Max value

QlikBeginner1 — Fri, 12 Mar 2021 15:13:06 GMT

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

Re: Table showing Max value

QlikBeginner1 — Fri, 12 Mar 2021 16:23:55 GMT

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