topic Re: Display Top 3 values of a chart in separate text boxes? in QlikView

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

Wed, 25 Nov 2020 16:16:04 GMT

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.

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

sunny_talwar — Tue, 21 Mar 2017 16:12:24 GMT

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)

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

Tue, 21 Mar 2017 16:17:57 GMT

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?

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

vishsaggi — Tue, 21 Mar 2017 16:20:33 GMT

May be use Match() Function.

LOAD *

FROM yourexcelfile

(....)

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

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

sunny_talwar — Tue, 21 Mar 2017 16:22:21 GMT

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

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

Tue, 21 Mar 2017 17:01:48 GMT

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

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

sunny_talwar — Tue, 21 Mar 2017 17:04:13 GMT

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)

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

Tue, 21 Mar 2017 17:07:21 GMT

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.

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

sunny_talwar — Tue, 21 Mar 2017 17:28:55 GMT

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