topic Set Analysis in QlikView

Set Analysis

Fri, 20 Apr 2012 14:16:24 GMT

What is the difference between the following expressions::::

1) Sum({$<Year = { "= $(vMaxYear) " } > } Sales)

and

2) Sum( {$<Year = {$(=vMaxYear) } >} Sales)

vMaxYear = Max(Year)

Set Analysis

Fri, 20 Apr 2012 16:27:07 GMT

Hi,

Sum({$<Year={"$(vMaxYear)"}>} Sales)

and

Sum( {$<Year={$(=vMaxYear)}>} Sales)

should behave the same.

in the first formula you don't need the equal sign and you must not have additional spaces within the quotes (see John Whiterspoon's posts about the dual type) or the comparison will fail : 2012 equals "2012" for qlikview, but the string "2012 " (additionnal space) will not match

If you add the heading equal sign as in your example above

Sum({$<Year={"=$(vMaxYear)"}>} Sales), le formula max(Year) will be evaluated for each and every row in your current selection.

Hope this helps

Hilaire

Re: Set Analysis

Fri, 20 Apr 2012 16:49:47 GMT

As a follow-up to my comment above,

you'll find attached a sample qlikview app to help you figure out the differences between several syntaxes.

Using single and multiple selections on the year dimension, you'll see the behaviour for each syntax.

In the last "simple table" graph, there's a formula that shows the differences between the "early evaluation" (no equal sign) and the "late evaluation" (using equal sign) of your vMaxYear variable / formula

Hope this helps

Hilaire

Re: Set Analysis

Fri, 20 Apr 2012 17:00:21 GMT

Hi,

(I would appreciate if Hilaire_qv as well other users can verify it, as it is my observation only and I could be wrong also)

As per my understanding, These two are entirely different set analysis.

Expression no. 1

Sum({$<Year = { "= $(vMaxYear) " } > } Sales)

is a advanced set analysis expression and becomes a conditional expression. So, this set analysis will select Year values whenever this expression returns true. (it will evaluate this expression for all values in your field)

In qlikview front end only 0 is taken as false while all other values are taken as true.

(say your max year is 2012) So, basically what happens is that after $ sign expansion it will become {" = 2012 "} or in other worlds it will become {true()} . and since it is always true then all the possible values in your year field will be selected (except null).

for example just search = 500 in your list box and it will select all your values but if you search = 0 no value will be selected even if you have a value as 0.

Expression no. 2

Sum( {$<Year = {$(=vMaxYear) } >} Sales)

is a basic set analysis expression which is not an evaluation expression and will simply search the values passed in curly braces.

when $ sign expansion is done it will become {2012} , so it will search for the 2012 in the year field and will sum all the sales values which are associated to 2012 as year.

Point to note here is that

1st expression in conditional while second is search.

if you want your first expression to return same result as the second then change it to

Sum({$<Year = { "= year = $(vMaxYear) " } > } Sales)

which will give you same results.

Ashutosh

Re: Set Analysis

Fri, 20 Apr 2012 17:04:20 GMT

Also, attached is a sample app for your these 2 set expressions.

Re: Set Analysis

Sat, 21 Apr 2012 08:43:42 GMT

Hi Ashutosh

Thanks for the explanation ... it really helped me a lot .... can you plz explain that

when i am selecting more than one year then why the expression Sum({$<Year = {"=$(vMaxYear)"}> } Sales) is giving me sum of Sales for all the years i have selected and secondly why the below expression do not work......

Sum({<Year = {"=$(vMaxYear)-1" }>}Sales)

Manish

Re: Set Analysis

Sat, 21 Apr 2012 09:48:44 GMT

Hi,

First of all both of them are going to be conditional expression for set analysis which comes under advanced set analysis and here you are not passing values to be searched in Year field rahter you are passing an expression and set analysis expects that you want results when this passed expression results true.

as, I sain in Qlikview front end only 0 is false all other values are considered true.

when i am selecting more than one year then why the expression Sum({$<Year = {"=$(vMaxYear)"}> } Sales) is giving me sum of Sales for all the years i have selected

This will always give you sum of all the possible values, so if you select 2 years it will give you sum of 2 years data and if you don't select any year then it will give you sum of all the possible values for all years (instead of max year's values sum)

as it is always giving you some value which is not 0. if you want it to return you max year's values then use Sum({$<Year = {"= Year=$(vMaxYear)"}> } Sales)

secondly why the below expression do not work......
Sum({<Year = {"=$(vMaxYear)-1" }>}Sales)

This expression should work, it will also give you the same result as the first expression. what are you getting in this expression.

Also, these results while using variables can vary depending on if you have used = in variable expression or not.

Please find the attached file.

Hope this should answer your question, if not then let me know!!

Set Analysis

Sat, 21 Apr 2012 14:57:12 GMT

Thanks bro... can you plz help me wth aggr function also .... why and when we use it????

Set Analysis

Sat, 21 Apr 2012 17:45:12 GMT

Hi,

aggr function is a function which is used when we need advanced aggregation.

Normally, aggregation functions such as sum will result in the sum of all the possible values. So, if we use it in a chart then it will give aggregation bifurcated (sum in case we use sum function) on the basis of chart's dimensions

and as well as based on the selections.

But, there could be scenarios when we don't want to display aggregations directly.

For example one scenario could be that you want to display the largest sales amount done on the basis of states.

but you have data available based on the cities.

So, if you put max(sales), it will not work because it will give you highest sales value according to city.

but if you put it like max(aggr(sum(sales),states)) then it will aggregate the values according to states and then will give you max amount.

the same scenario can be thought of that in a chart you are not going to display states as dimension but you will use city, one column needs amount to be shown as city wise but there is one more column in which amount is needed to shown for state in which that city is?

then again, aggr function will help you.

So, points to note here are;

aggr function can do aggregations based on the field values passed which is not possible without aggr.

you can not use one aggregation function on another aggregation function, so if you write max(sum(sales)) this is not a vaild syntax in qlikview., in these scenarios aggr can be handy because with aggr you can do this also as I did in the max(aggr(sum(sales),states))

Hope this helps.

Ashutosh

Set Analysis

Sat, 21 Apr 2012 17:46:58 GMT

Also, to see, how to use aggr refer to help, there it is explained with the examples also.

Ashutosh