topic Re: Rank 10 highest values in a table with two dimensions in App Development

Rank 10 highest values in a table with two dimensions

helge_jorg — Thu, 28 Jun 2018 10:16:22 GMT

Qlik experts

I would like to rank my measures to only show the 10 largest values in a table.

My dimesion is Date and [Navn]

The measure is avg({<ElementID= {TAX}>} [Verdi])

Thanks!

Cheers

Helge

Re: Rank 10 highest values in a table with two dimensions

brunobertels — Thu, 28 Jun 2018 10:42:30 GMT

May be this as mesure

If(

Rank(

avg({<ElementID= {TAX}>} [Verdi]) ) > 10 ,

avg({<ElementID= {TAX}>} [Verdi])

)

Re: Rank 10 highest values in a table with two dimensions

sunny_talwar — Thu, 28 Jun 2018 11:22:04 GMT

Top 10 for each Date or overall top 10 across both the dimensions? If it is over all top 10.. then try this

If(Rank(TOTAL Avg({<ElementID= {TAX}>} [Verdi])) < 11, Avg({<ElementID= {TAX}>} [Verdi]))

Re: Rank 10 highest values in a table with two dimensions

helge_jorg — Thu, 28 Jun 2018 11:24:35 GMT

Hi, Bruno

Your measure expression did not work as I was hoping for. The expression greys out the then highest values, and the rest is displayed. In addition, the table includes the entire record of measures.

Re: Rank 10 highest values in a table with two dimensions

helge_jorg — Thu, 28 Jun 2018 11:29:53 GMT

Hi, Sunny

Top 10 for each date, not both dimensions. Your expression works fine, the top 10 values are listed. But the rest of the values in the table is grey. It there a way to only show the top 10 values in the table, and exclude the rest?

Re: Rank 10 highest values in a table with two dimensions

sunny_talwar — Thu, 28 Jun 2018 11:33:37 GMT

Can you share an image of what you are seeing?

Re: Rank 10 highest values in a table with two dimensions

sunny_talwar — Thu, 28 Jun 2018 11:33:55 GMT

Try this for top 10 for each date

If(Rank(Avg({<ElementID= {TAX}>} [Verdi])) < 11, Avg({<ElementID= {TAX}>} [Verdi]))

Re: Rank 10 highest values in a table with two dimensions

brunobertels — Thu, 28 Jun 2018 11:38:55 GMT

sorry my bad

try this

If(

Rank(

avg({<ElementID= {TAX}>} [Verdi]) ) < 11 ,

avg({<ElementID= {TAX}>} [Verdi])

)

Re: Rank 10 highest values in a table with two dimensions

helge_jorg — Thu, 28 Jun 2018 11:39:37 GMT

Re: Rank 10 highest values in a table with two dimensions

sunny_talwar — Thu, 28 Jun 2018 11:43:18 GMT

Do you have more then 1 measure in your chart? If you do... then use the same condition for all the expressions

If(Rank(Avg({<ElementID= {TAX}>} [Verdi])) < 11, Measure1)

If(Rank(Avg({<ElementID= {TAX}>} [Verdi])) < 11, Measure2)

etc

and also, make sure that you have unchecked 'Include Zero Values' under Add-Ons -> Data Handling.

Re: Rank 10 highest values in a table with two dimensions

helge_jorg — Thu, 28 Jun 2018 12:07:36 GMT

The uncheck "Include Zero Values" did it!

Thanks for the help, Sunny!

PS: There is only one measure, the other ones are date (year, month and day) and name dimensions.

Re: Rank 10 highest values in a table with two dimensions

helge_jorg — Thu, 28 Jun 2018 16:12:54 GMT

I have one additional question – how can rank the measures by lowest value? The measure includes negative values (ex. -42,80 degrees), depending on which month.

When I use this expression: If(Rank(TOTAL -Avg({<ElementID= {'TAX'}>} [Verdi])) < 11, -Avg({<ElementID= {'TAX'}>} [Verdi])) I get the rank as would like, but the negative value will then be displayed as a positive value.

Re: Rank 10 highest values in a table with two dimensions

sunny_talwar — Thu, 28 Jun 2018 16:15:00 GMT

Do this without the negative sign before your actual expression... keep the one within the Rank()

If(Rank(TOTAL -Avg({<ElementID= {'TAX'}>} [Verdi])) < 11, Avg({<ElementID= {'TAX'}>} [Verdi]))

Re: Rank 10 highest values in a table with two dimensions

DirkPeterCasteleijn — Mon, 04 Jan 2021 20:09:43 GMT

Hey Sunny,

I got the error: 'If-statement requires 2 or more parameters', and if i look to the expression this right.

Is the expression you provided just the syntax of the expression itselve?