Restricting Data Basis for Scatter Chart

[#1]

Hello,

i have two scatter charts.

Each has to use different data basis ie - i have only one table and the first chart has to use the whole dataset, and the second chart has to use only subset of the initial dataset.

My question is - when defining dimension in scatter chart (it's actually bubble chart) how can I make this SUBSET available to the chart?
Now both charts have the following dimensin "Unique_ID".

The second chart has to have Unique_ID when the condition is met - when values in a column  "M" are not NULL and are not empty strings.

How can I implement this behaviour? With set analysis syntax?

Thx!

[#2]

Jacob is basically saying that sometimes the editor will say an expression is invalid, when, in fact, it is a correct and legitimate expression that runs completely fine

[#3]

I have a open url action associated with a button, and within this I use dollar sign expansion with a subfield(concat()) statement. Towards the end of the concat statement and for the rest of my url expression everything is underlined red. However at the top left it says Expression OK

[#4]

I am not getting the output I was expecting.

The expression I am using is:

If( Len(Trim( M )) > 0

OR

M <> 0,

     do-the-calculation-you-should-do

)

So, when I select "0" from the field, I get the same dots on the left graph (which should take the reduced data set and where I did implemented the formula above) as in the right graph (the whole dataset is basis for this graph.)

By the way - the M attribute is defined as INTEGER in database.

[hic · Accepted solution]

You need an aggregation function around your expression. http://community.qlik.com/blogs/qlikviewdesignblog/2013/08/06/it-s-all-aggregations

And you you should probably change your "OR" to an "AND".

HIC

[#6]

Many thanks Henric! It worked when I changed "OR" operator into "AND".
However, why should I use an  aggregation function?

Here is my complete expression:

If( Len(Trim( DSL_BB_Down )) > 0

AND

DSL_BB_Down <> 0,

round (256*pow(2,($(var_zoom)-1)))+( avg(Longitude)  *((256*pow(2,$(var_zoom)))/360))

)

As you can see, the expression itself (round...) is complex.
The way it is constructed now, it will do the calculation only when the IF conditon is met.

[hic]

If there always is one and only one value of DSL_BB_Down per dimensional value, then your expression will work fine.

However if you have several values, your expression will not work. Then it is better to write a formula that always work.

See more on http://community.qlik.com/blogs/qlikviewdesignblog/2013/08/06/it-s-all-aggregations

HIC

[#8]

If I understood you correctly, the expressions for longitude (X-axis) and lattitude (Y-axis) should look as follows:

Longitude:
old version: round (256*pow(2,($(var_zoom)-1)))+( avg(Longitude)  *((256*pow(2,$(var_zoom)))/360))       

new version: round (256*pow(2,($(var_zoom)-1)))+( Avg( If( Len(Trim( Longitude )) >0 AND DSL_BB_Down <> 0, Longitude )) *((256*pow(2,$(var_zoom)))/360))

What is with the ROUND function in this case? Do I need to wrap it inside some aggregation function as well?

Lattitude:

old version:  ((256*pow(2,($(var_zoom)-1)))+((0.5*log((1+(sin((avg(Latitude))*pi()/180)))/(1-(sin((avg(Latitude))*pi()/180)))))*((-256*pow(2,$(var_zoom)))/(2*pi()))))         

new version:

((256*pow(2,($(var_zoom)-1)))+((0.5*log((1+(sin((Avg( If( Len(Trim( Latitude )) >0 AND DSL_BB_Down <> 0, Latitude )))*pi()/180)))/(1-(sin((Avg( If( Len(Trim( Latitude )) >0 AND DSL_BB_Down <> 0, Latitude )))*pi()/180)))))*((-256*pow(2,$(var_zoom)))/(2*pi())))) 

[hic]

You can have the Round() as your outermost function, i.e. outside the aggregation function.

HIC

[#10]

Does that mean that the expression for longitude is built as you suggested in your article, and that I only need to edit the expression for longitude by adding ROUND(---original_expression---) function for the original expression?

Second question is: do you suggest this approach, or approach that includes defining a variable that contains the mentioned condition, and working with set?