Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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
MVP
MVP

Re: Scatter chart - auto spacing the bubbles

Try

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

10 Replies
MVP
MVP

Re: Scatter chart - auto spacing the bubbles

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

Re: Scatter chart - auto spacing the bubbles

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

Re: Scatter chart - auto spacing the bubbles

How do I attach a .qvw file?

MVP
MVP

Re: Scatter chart - auto spacing the bubbles

Uploading a Sample

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

Not applicable

Re: Scatter chart - auto spacing the bubbles

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. 

MVP
MVP

Re: Scatter chart - auto spacing the bubbles

Maybe like this using y-expression

=Aggr(RowNo(),ContinentID,Country)

Not applicable

Re: Scatter chart - auto spacing the bubbles

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

MVP
MVP

Re: Scatter chart - auto spacing the bubbles

Try

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

Not applicable

Re: Scatter chart - auto spacing the bubbles

This worked perfectly!!!

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

Community Browser