
Set Analysis
Hilaire GODINOT Apr 20, 2012 12:27 PM (in response to Manish Kukreti)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
Ashutosh Paliwal Apr 20, 2012 1:13 PM (in response to Hilaire GODINOT )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
Hilaire GODINOT Apr 20, 2012 12:49 PM (in response to Manish Kukreti)As a followup 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

sample_app_formulas.qvw 144.5 K


Re: Set Analysis
Ashutosh Paliwal Apr 20, 2012 1:04 PM (in response to Manish Kukreti)Also, attached is a sample app for your these 2 set expressions.

t.qvw 149.0 K

Re: Set Analysis
Manish Kukreti Apr 21, 2012 4:43 AM (in response to Ashutosh Paliwal)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
Ashutosh Paliwal Apr 21, 2012 5:51 AM (in response to Manish Kukreti)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!!

t.qvw 156.0 K

Set Analysis
Manish Kukreti Apr 21, 2012 10:57 AM (in response to Ashutosh Paliwal)Thanks bro... can you plz help me wth aggr function also .... why and when we use it????

Set Analysis
Ashutosh Paliwal Apr 21, 2012 1:45 PM (in response to Manish Kukreti)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
Ashutosh Paliwal Apr 21, 2012 1:46 PM (in response to Manish Kukreti)Also, to see, how to use aggr refer to help, there it is explained with the examples also.
..
Ashutosh



