Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Scatter chart - auto spacing the bubbles

I'm trying to get a bubble chart that will not overlap the bubbles.  Instead it should space and center the bubbles.

And it should respect my filter (so I can't do it in the script)

For example, lets say I want to plot countries by continent.

My data would be:

Country / Continent

USA / North America

Canada / North America

Mexico / North America

Venezuela / South America

Peru / South America

Argentina / South America

Brazil / South America

Ecuador / South America

Italy / Europe

I would imagine the solution would be:

For Spacing:  Auto numbering the countries by continent (perhaps with a combinantion of Aggr/RowNo/Range).

Then for Centering:  Adding (half of) the maximum count of countries per continent minus the max amount for that continent.

So, since there are 3 in North Am., 5 in South Am., and 1 in Europe, I would get:

Country / Continent / Position

USA / North America = 1 + 5 - (5-3)/2 = 2

Canada / North America = 2 + 5 - (5-3)/2 = 3

Mexico / North America = 3 + 5 - (5-3)/2 = 4

Venezuela / South America = 1 + 5 - (5-5)/2 = 1

Peru / South America = 2 + 5 - (5-5)/2 = 2

Argentina / South America = 3 + 5 - (5-5)/2 = 3

Brazil / South America = 4 + 5 - (5-5)/2 = 4

Ecuador / South America = 5 + 5 - (5-5)/2 = 5

Italy / Europe = 1 + 5 - (5-1)/2 = 3

Then I can plot this on a scatter.

So if someone can help with that calculation, that would be great.

Of course I would welcome any other way to get this chart.

1 Solution

Accepted Solutions
swuehl
MVP
MVP

Try

=Aggr(RowNo()-NoOfRows()/2,ContinentID,Country)+Max(TOTAL Aggr(Count( DISTINCT Country), ContinentID))/2

View solution in original post

10 Replies
swuehl
MVP
MVP

Not really sure if I can follow what you've described here.

A scatter chart will plot bubbles at numeric x/y coordinates. How do you derive x and y from above?

Maybe you can post a small sample QVW with a scatter chart, as far as you can get. And if possible, with a description (sketch / coordinates) to show where to exactely position the bubbbles.

Maybe you just want to look into using a grid chart instead?

Regards,

Stefan

Not applicable
Author

If it can be done in a grid chart, great.  The part that I'm having trouble with is calculating the y axis bubble position.  I would like all my bubbles spaced out and centered--and not overlapping.

Attached is an example with what I have so far (top graph), and what I want (bottom graph).  Note:  the bottom graph uses a cheat that I hardcoded into the script.  I can't use the script to calculate because I want it to recalculate based on current filters.

Not applicable
Author

How do I attach a .qvw file?

swuehl
MVP
MVP

Uploading a Sample

And please also try to explain your expected result, I am still not understanding your issue 100%.

Not applicable
Author

Attached is the file.  I want the real graph to look like the bottom graph.  The y axis is basically a count of the maximum number of countries in the group, with each country getting a unique number within the group.  This way I can space out my bubbles so they never overlap. 

swuehl
MVP
MVP

Maybe like this using y-expression

=Aggr(RowNo(),ContinentID,Country)

Not applicable
Author

That actually worked for spacing out the bubbles!!!  Thank you.

Now the next step is centering.

Your formula numbered North America 1,2,3 and put it at the bottom of the chart.  Since South America has 5 countries, I want North America to be numbered 2,3,4.  And Italy just to be centered at #3.

So we would need to add (Max count per continent - Max count for North America)/2 to each North America element.  The following seems close, but I'm just missing the last piece:

=Aggr(RowNo(),ContinentID,Country) +  ( Max(TOTAL(Aggr(Count(Country),Continent)))  -  ????) / 2

swuehl
MVP
MVP

Try

=Aggr(RowNo()-NoOfRows()/2,ContinentID,Country)+Max(TOTAL Aggr(Count( DISTINCT Country), ContinentID))/2

Not applicable
Author

This worked perfectly!!!

Extra credit:  is there anyway I can replace the number on my x axis with the Continent Name?