topic Re: Max Count displaying field name in QlikView

Max Count displaying field name

rido1421 — Thu, 05 Jun 2014 13:38:45 GMT

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 .

Re: Max Count displaying field name

Thu, 05 Jun 2014 13:40:58 GMT

Hi Rido

something like

=FirstSortedValue([Reason Code ],

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

Chris

Re: Max Count displaying field name

its_anandrjs — Thu, 05 Jun 2014 13:42:16 GMT

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])) )

Re: Max Count displaying field name

rido1421 — Thu, 05 Jun 2014 13:49:00 GMT

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

Re: Max Count displaying field name

MK_QSL — Thu, 05 Jun 2014 20:13:10 GMT

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

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

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

Re: Max Count displaying field name

rido1421 — Fri, 06 Jun 2014 04:53:50 GMT

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

Re: Max Count displaying field name

Fri, 06 Jun 2014 07:28:05 GMT

Rido,

glad to help you

to close the case tick it as correct

best regards

Chris

Re: Max Count displaying field name

cbaqir — Mon, 05 Jan 2015 22:29:05 GMT

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