topic Re: Scatter chart - auto spacing the bubbles in QlikView

Scatter chart - auto spacing the bubbles

Thu, 14 Apr 2016 20:30:19 GMT

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.

Re: Scatter chart - auto spacing the bubbles

swuehl — Thu, 14 Apr 2016 21:49:27 GMT

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

Re: Scatter chart - auto spacing the bubbles

Fri, 15 Apr 2016 13:43:31 GMT

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.

Re: Scatter chart - auto spacing the bubbles

Fri, 15 Apr 2016 13:52:23 GMT

How do I attach a .qvw file?

Re: Scatter chart - auto spacing the bubbles

swuehl — Fri, 15 Apr 2016 17:18:10 GMT

Uploading a Sample

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

Re: Scatter chart - auto spacing the bubbles

Fri, 15 Apr 2016 17:39:33 GMT

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.

Re: Scatter chart - auto spacing the bubbles

swuehl — Fri, 15 Apr 2016 17:54:40 GMT

Maybe like this using y-expression

=Aggr(RowNo(),ContinentID,Country)

Re: Scatter chart - auto spacing the bubbles

Fri, 15 Apr 2016 18:48:22 GMT

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

Re: Scatter chart - auto spacing the bubbles

swuehl — Fri, 15 Apr 2016 19:04:21 GMT

Try

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

Re: Scatter chart - auto spacing the bubbles

Fri, 15 Apr 2016 19:25:27 GMT

This worked perfectly!!!

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

Re: Scatter chart - auto spacing the bubbles

swuehl — Fri, 15 Apr 2016 20:59:36 GMT

I think that's not possible with a scatter chart, having numeric, continuous axes.

Similar to a line chart's continuous axis, you are bound to number formatting using num() or using the formatting options on number tab.

You could try with some hacks like overlaying chart objects or something like this.