topic Re: Restricting Data Basis for Scatter Chart in QlikView

Restricting Data Basis for Scatter Chart

Fri, 06 Jun 2014 13:37:02 GMT

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!

Re: Restricting Data Basis for Scatter Chart

hic — Fri, 06 Jun 2014 14:14:41 GMT

The expressions in a Scatter chart are the x and y positions of the dots. If you use an expression like

Avg( If( Len(Trim( M )) >0, Xpos ))

you will restrict the number of data points just like you want.

To use set analysis, it is simplest if you define the above condition as a field in the script:

If( Len(Trim( M )) >0, 1, 0 ) as M_IsOK

and then use the following expression

Avg( {$<M_IsOK={1}>} Xpos )

HIC

Re: Restricting Data Basis for Scatter Chart

Fri, 06 Jun 2014 14:20:18 GMT

Hi Henric

Is it best practice to always put the $ identifier rather than leave it blank? I am hosting a webinar today on set analysis and would like to clarify that if there is a compelling reason to include rather than exclude as the default.

Re: Restricting Data Basis for Scatter Chart

Fri, 06 Jun 2014 14:22:27 GMT

The $ identifier denotes that you're specifying the set that you're looking at are the current selections. In most cases you'll probably want to use that

Re: Restricting Data Basis for Scatter Chart

hic — Fri, 06 Jun 2014 14:28:46 GMT

No, there is no compelling reason in one or the other direction.

I personally think it is important to be explicit - so my code and my formulas are easy to read. So I always use {$<...>} instead of {<...>}, so the dollar sign is clearly visible. But the two expressions are in most cases the same.

The only case when they are not the same, is if you use alternate states. Then {$<...>} means default state whereas {<...>} means inherited state.

HIC

Re: Restricting Data Basis for Scatter Chart

Fri, 06 Jun 2014 14:38:08 GMT

thank you Henric! One more question I want to clarify before my webinar.

the -= (not equal to) is always followed by the red underline indicating an error in the formula even though it works. Is this a bug or is there another step needed to take to remove this. I have just lived with it over the years.

ok, maybe 2 questions

In addition, when you put in a field name in an aggregate function and you do not get the case correct but you spelled it correctly, it will not give you an error indicator but the expression will obviously not work. We found this at at the first master summit and it took us a bit to notice the case problem. Is this also a bug or is it just not possible for it to recognize case.

Re: Restricting Data Basis for Scatter Chart

Fri, 06 Jun 2014 14:46:37 GMT

Thank you Robert. Using the $ is the same as omitting it so i was wanting to confirm if there is any reason at all that would benefit by including rather than omitting it as default.

Re: Restricting Data Basis for Scatter Chart

Fri, 06 Jun 2014 14:53:14 GMT

I have found in my limited experience that the editor is not always accurate.

Re: Restricting Data Basis for Scatter Chart

Fri, 06 Jun 2014 15:02:39 GMT

Jacob, would love to get your feedback on other things you may have noticed. So far I have only experienced the

-= issue and the case issue

I have been working with Qlikview for about 7 years now I think, I am losing track but I am still learning new things every single day. There is so much flexibility with Qlikview.

Re: Restricting Data Basis for Scatter Chart

hic — Fri, 06 Jun 2014 15:59:14 GMT

I think you could classify both as bugs. The problem is that the color coding doesn't always follow syntax...

HIC

Re: Restricting Data Basis for Scatter Chart

Fri, 06 Jun 2014 19:12:28 GMT

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

Re: Restricting Data Basis for Scatter Chart

Fri, 06 Jun 2014 19:25:48 GMT

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

Re: Restricting Data Basis for Scatter Chart

Tue, 10 Jun 2014 15:18:15 GMT

I am not getting the output I was expecting.

The expression I am using is:

If( Len(Trim( M )) > 0

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.

Re: Restricting Data Basis for Scatter Chart

hic — Tue, 10 Jun 2014 15:41:01 GMT

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

Re: Restricting Data Basis for Scatter Chart

Wed, 11 Jun 2014 08:23:47 GMT

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.

Re: Restricting Data Basis for Scatter Chart

hic — Wed, 11 Jun 2014 08:52:39 GMT

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

Re: Restricting Data Basis for Scatter Chart

Wed, 11 Jun 2014 11:22:36 GMT

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()))))

Re: Restricting Data Basis for Scatter Chart

hic — Wed, 11 Jun 2014 11:25:12 GMT

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

HIC

Re: Restricting Data Basis for Scatter Chart

Wed, 11 Jun 2014 12:16:26 GMT

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?