Max Count displaying field name

[rido1421]

Hi

I would like to do a max count but instead of displaying the count I would like to display the field name of the highest count

Max( aggr( count([Reason Code ]), [Reason Code ]))  This gives me the count of the highest reason code, but I need to display the name of the highest reason code

Thank you .

[#2 · Accepted solution]

Hi Rido

something like

=FirstSortedValue([Reason Code ],

( -aggr( count([Reason Code ]), [Reason Code ]))

Chris

[its_anandrjs]

Update

Try with this

This is used to find Max number

=Max( Aggr( count ( [Reason Code] ), [Reason Code] ))

But i believe you have to use

=FirstSortedValue([Reason Code],-(aggr( count([Reason Code]), [Reason Code])) )

[rido1421]

Hi it doesn’t seem to be working

What do you mean with the below?

,[Your Reason Name Here]

Ridhaa Hendricks

Data Analyst

0842276595

r.hendricks@cellc.co.za

This email and its contents are subject to our email legal notice which can be viewed at http://www.cellc.co.za/dl/cms/downloads/Email_legal_notice.pdf

[MK_QSL]

FirstSortedValue([Region Name], -Aggr(COUNT([Region Code]), [Region Code])

or

FirstSortedValue([Region Name], -COUNT([Region Code]))

or

FirstSortedValue([Region Name], -Aggr(COUNT([Region Code]), [Region Name])

[rido1421]

Thank You Christianj! This worked perfect! ☺

Ridhaa Hendricks

Data Analyst

0842276595

r.hendricks@cellc.co.za

This email and its contents are subject to our email legal notice which can be viewed at http://www.cellc.co.za/dl/cms/downloads/Email_legal_notice.pdf

[#7]

Rido,

glad to help you

to close the case tick it as correct

best regards

Chris

[cbaqir]

Hi Chris,

I saw this response using the FirstSortedValue and I was hoping that was close to a solution to the problem I am having.

I have an app in which I need to display the count of the current workflow status for distinct REQUEST_ID.

To determine current workflow status, I am looking at max(WORKFLOW_STEP_ELIG_DATE) and then want a count of the associated WORKFLOW_STEP_NAME.

I started with:
=count(aggr(max(WORKFLOW_STEP_ELIG_DATE), REQUEST_ID))

And then tried applying the FirstSortedValue expression but it didn't work because I can't use nested aggregation to get the MAX ELIG_DATE:

=FirstSortedValue([WORKFLOW_STEP_NAME], (-Aggr(Count( DISTINCT WORKFLOW_STEP_ELIG_DATE), REQUEST_ID)))

Any guidance would be greatly appreciated. Thanks in advance!

Cassandra