Display Top 3 values of a chart in separate text boxes?

[#1]

I want to display top 3 values in a straight table/chart in 3 text boxes. I am able to print the dimension but not the expression.

My exact requirement is to have a text box displaying the value and a text below displaying dimension.

eg:     $1500 - This in one Text Box

          USA - This in Another Text Box.

I am able to display "USA" using the following formula:

=CONCAT(DISTINCT IF(AGGR(Rank(count([Acct Type 2: Business type (use drop-down)])),[Country])=1, [Country]&chr(13)))

Can anyone please guide me how to print the value?

Thanks in advance.

[sunny_talwar · Accepted solution]

May be these

1) Max(Aggr(Count([Acct Type 2: Business type (use drop-down)]), Country))

2) Max(Aggr(Count([Acct Type 2: Business type (use drop-down)]), Country), 2)

3) Max(Aggr(Count([Acct Type 2: Business type (use drop-down)]), Country), 3)

[#3]

Thank You Sunny this works perfectly.

I just had one more query related to this, I am importing my data from excel but I dont want to include certain countries in my analysis in qlik, is there any way I can adjust my dimension only to use the countries that i want in qlik rather than excel?

[vishsaggi]

May be use Match() Function.

LOAD *

FROM yourexcelfile

(....)

WHERE Match(Country, 'USA', 'UK', 'Germany');

[sunny_talwar]

Or Where not Match(Country, 'CountryA', 'CountryB')

[#6]

Thank you Sunny and Vishwarath, But is there any way to include that in this expression instead to complete workbook?

[sunny_talwar]

Sure this

Max({<Country -= {'CountryA', 'CountryB'}>}Aggr(Count({<Country -= {'CountryA', 'CountryB'}>}[Acct Type 2: Business type (use drop-down)]), Country))

Max({<Country -= {'CountryA', 'CountryB'}>}Aggr(Count({<Country -= {'CountryA', 'CountryB'}>}[Acct Type 2: Business type (use drop-down)]), Country), 2)

Max({<Country -= {'CountryA', 'CountryB'}>}Aggr(Count({<Country -= {'CountryA', 'CountryB'}>}[Acct Type 2: Business type (use drop-down)]), Country), 3)

[#8]

Sunny, I am sorry I was not able to put up my point, The list of countries not to be selected is very long, so it would be difficult to add all of their names, can we have a formula where we can add the countries we want to include?

Thank you so much in advance.

[sunny_talwar]

You can create a new field which will include all the countries which needs to be Excluded and then do something like this

Max({<Country = e(Excluded_Country)>}Aggr(Count({<Country = e(Excluded_Country)>}[Acct Type 2: Business type (use drop-down)]), Country))