topic Re: Display Rank as Pop Up? in QlikView

Display Rank as Pop Up?

Wed, 25 Nov 2020 16:16:04 GMT

How can i display Top 3 values for a particular field in pop up for all the bars in that barchart.

Detail:

I have created a bar chart with following dimensions and measure:

Country and Value

Now in pop up for all the countries I want to display top 3 countries with highest value i.e. if top 3 countries by value are USA, UK, Japan.... even for Brazil it should show USA, UK, Japan in order ... for all countries.

Re: Display Rank as Pop Up?

Kushal_Chawda — Sun, 02 Apr 2017 15:48:16 GMT

Front end solution could be like below

Create variable vPopUpFronEnd on front end with below expression

=Concat(aggr(if(rank(sum(Sales),4)<=3,Country&' : '&sum(Sales)),Country),chr(10),aggr(rank(sum(Sales),4),Country))

Now in Bar chart create the below expression as "Text as Pop up"

=dual(chr(10)&'Top 3 Sales'&chr(10)&'$(vPopUpFronEnd)', Sum(Sales))

Back end solution could be like below

Data:

LOAD * Inline [

Country,Sales

US,300

UK,250

JAPAN,210

BRAZIL,220

INDIA,230];

New:

LOAD Concat(Top3Country&' : '&Top3CountrySales,chr(10),Sort) as Popup

Where Sort<=3;

LOAD Country as Top3Country,

Sum(Sales) as Top3CountrySales,

autonumber(Sum(Sales)) as Sort

Resident Data

Group by Country

Order by Sales desc;

LET vPopUpBackend = Peek('Popup',0,'New');

Now in Bar chart create the below expression as "Text as Pop up"

=dual(chr(10)&'Top 3 Sales'&chr(10)&'$(vPopUpBackend )', Sum(Sales))

See the attached app

Re: Display Rank as Pop Up?

Sun, 02 Apr 2017 16:04:19 GMT

Hi Kushal,

Thank you so much this works perfectly. Can you also advise me how to format the value to get Money number Format please?

Re: Display Rank as Pop Up?

Kushal_Chawda — Sun, 02 Apr 2017 16:33:00 GMT

you can use Money function

=Concat(aggr(if(rank(sum(Sales),4)<=3,Country&' : '&money(sum(Sales),'$#,##0')),Country),chr(10),aggr(rank(sum(Sales),4),Country))

Re: Display Rank as Pop Up?

Kushal_Chawda — Sun, 02 Apr 2017 17:11:51 GMT

Please close the thread by marking answer as correct.

Re: Display Rank as Pop Up?

Mon, 03 Apr 2017 13:46:00 GMT

Hello Kushal,

Can you please tell me what does "4" denote in the below formula?

=Concat(aggr(if(rank(sum(Sales),4)<=3,Country&' : '&money(sum(Sales),'$#,##0')),Country),chr(10),aggr(rank(sum(Sales),4),Country))

Re: Display Rank as Pop Up?

Kushal_Chawda — Mon, 03 Apr 2017 14:27:17 GMT

It is one of the parameter of Rank function, which is mode. For more details see the below content from qlikview help.

You can also search on community for the same for better understanding.

rank([ total ] expression [ , mode [, format ] ])

Evaluates expression, compares the result with the result of the other rows containing the current column segment and returns the ranking of the current row within the segment. For bitmap charts, the current column segment is defined as it appears in the chart's straight table equivalent (Actually all QlikView charts have a straight table equivalent with the exception of the pivot table which has a more complex structure).

If the chart is one-dimensional or if the expression is preceded by the total qualifier, the current column segment is always equal to the entire column. If the table or table equivalent has multiple vertical dimensions, the current column segment will include only rows with the same values as the current row in all dimension columns except for the column showing the last dimension in the inter field sort order.

The ranking is returned as a dual value, which in the case when each row has a unique ranking will be an integer between 1 and the number of rows in the current column segment.

In the case where several rows share the same ranking, the text and number representation can be controlled as follows:

The second parameter mode specifies the number representation of the function result.

mode

0 (default)	If all ranks within the sharing group fall on the low side of the middle value of the entire ranking, all rows get the lowest rank within the sharing group. If all ranks within the sharing group fall on the high side of the middle value of the entire ranking, all rows get the highest rank within the sharing group. If ranks within the sharing group span over the middle value of the entire ranking, all rows get the value corresponding to the average of the top and bottom ranking in the entire column segment.
1	Lowest rank on all rows.
2	Average rank on all rows.
3	Highest rank on all rows.
4	Lowest rank on first row, then incremented by one for each row.

Re: Display Rank as Pop Up?

Mon, 03 Apr 2017 14:41:38 GMT

Thank you so much Kushal,

I have a request can you guide me on how to change this formula when there are sub categories?

Eg: I have a 2nd chart where I display value by categories like Finance, Defense, etc of countries. Should i use set analysis in the formula?

The values in the pop should also change if I apply filter for any of these categories

Re: Display Rank as Pop Up?

Kushal_Chawda — Mon, 03 Apr 2017 15:10:39 GMT

Can you provide example like which chart you are having and how you are going to do the selection and what pop up should show? A sample application would be also helpful

Re: Display Rank as Pop Up?

Mon, 03 Apr 2017 15:16:16 GMT

The data looks something like this:

Continent, Country, Division, Value

Asia, Japan, Finance, 100

Asia, Japan, Defense, 130

North America, USA, Finance, 110

Europe, UK, Defense, 200

....

...

Now, I have a stacked chart which shows country and Division as Dimensions with division stacked up in country. I want to show in pop up, Top 3 Countries for that particular division when I hover over that division in that country, even though That country is not in top 3. Right now with the formula we have I am able to display top 3 countries by total value but not by division.

Hope this helps!!

Please let me know if you need more details.

Thank you so much once again.

Re: Display Rank as Pop Up?

Mon, 03 Apr 2017 17:03:37 GMT

Hi Kushal,

Can we connect via email for this please if possible?

Thanks,

Tanmay

Re: Display Rank as Pop Up?

Kushal_Chawda — Mon, 03 Apr 2017 18:19:49 GMT

see this below which is back end solution.

Data:

LOAD * Inline [

Country,Sales,Divison

US,300,Finance

US,170,Defence

UK,250,Finance

UK,150,Defence

JAPAN,210,Finance

JAPAN,140,Defence

BRAZIL,220,Finance

BRAZIL,160,Defence

INDIA,230,Finance

INDIA,130,Defence];

New:

LOAD *

Where Sort<=3;

LOAD Country as Top3Contry,

Divison,

Sum(Sales) as Top3CountrySales,

autonumber(Sum(Sales),Divison) as Sort

Resident Data

Group by Country,Divison

Order by Sales desc;

LET vPopUpBackend = Peek('Popup',0,'New');

Popup expression would be

=dual(chr(10)&'Top 3 Sales'&chr(10)&Concat(Top3Contry&' : '&Top3CountrySales,chr(10),Sort),Sum(Sales))

Re: Display Rank as Pop Up?

Kushal_Chawda — Tue, 04 Apr 2017 04:51:25 GMT

You can mark the above answer as helpful