topic Re: How to use variable in set analysis ? in QlikView

How to use variable in set analysis ?

Thu, 05 Mar 2015 14:06:45 GMT

Hello everyone,

In my application, there are data for several years and I would like to show in a graph only the last 3 years (current one + 2 laters).

So I created a variable : vCurrentYear = max(MyYear) // MyYear is a field in my calendar that goes until the date of execution.

If I put =$(vCurrentYear) in a textbox, I do get 2015.

In my graph, in the expression tab, I have entered in order to get the sum of Cpt_abt for the past 3 years.

sum({$<Year_S={">=$(vCurrentYear)-2"}>}Cpt_abt)

But I get the message "No data to display".

If instead, I put

sum({$<Year_S={">=2013"}>}Cpt_abt) , I do get my graph.

I am guessing there must be something wrong with the quotations marks but I cannot find the proper way to write my expression..

Any ideas ?

Thank you for your help

Re: How to use variable in set analysis ?

datanibbler — Thu, 05 Mar 2015 14:12:05 GMT

Hi,

try an alternative way of building your set_analysis, a little less elegant, but more robust - and the script_editor knows that.

>> sum({<Year_S = {"$(= '>=' & vCurrentYear)"}>} <<

You might need to put that variable vCurrentYear into another $() - I don't know for sure - but I guess not as you can only do this on the GUI anyway and vCurrentYear is a numeric value which usually doesn't require that.

HTH

Re: How to use variable in set analysis ?

sunny_talwar — Thu, 05 Mar 2015 14:12:47 GMT

I would try this and see if this work (I can't try it since I don't have any application opened)

sum({$<Year_S={'>=$(=$(vCurrentYear)-2)'}>}Cpt_abt)

Have never tried 2 $ signs before, but may work. But if it doesn't then I would suggest another variable for Year-2, may be vPrevYear2 = Max(Year) - 2 and use that in your modifier as follows:

sum({$<Year_S={'>=$(vPrevYear2)'}>}Cpt_abt)

HTH

Best,

Re: How to use variable in set analysis ?

chrislofthouse — Thu, 05 Mar 2015 14:13:40 GMT

Try this:

sum({$<Year_S={">=$(=$(vCurrentYear)-2)"}>}Cpt_abt)

Re: How to use variable in set analysis ?

luciancotea — Thu, 05 Mar 2015 14:15:54 GMT

sum({$<Year_S={">=$(=vCurrentYear-2)"}>}Cpt_abt)

Re: How to use variable in set analysis ?

Colin-Albert — Thu, 05 Mar 2015 14:19:20 GMT

To apply the calculation in the set expression you need to enclose the calculation within $(= )

sum({$<Year_S={">=$(= $(vCurrentYear) -2 )"}>}Cpt_abt)

Re: How to use variable in set analysis ?

jonathandienst — Thu, 05 Mar 2015 14:20:58 GMT

Define vCurrentYear to evaluate globally. That means the definition should have an = sign in front:

=max(MyYear)

Now in the text box, you should be able to enter

=vCurrentYear

end get the year. Then use this expression (note the = sign)

sum({$<Year_S = {">=$(=vCurrentYear - 2)"}>} Cpt_abt)

HTH

Jonathan

Re: How to use variable in set analysis ?

sunny_talwar — Thu, 05 Mar 2015 14:21:40 GMT

So Colin_Albert , double $'s do work in the modifier? Never tried them before, but I was curious.

Best,

Re: How to use variable in set analysis ?

Colin-Albert — Thu, 05 Mar 2015 14:25:38 GMT

The $() around the variable forces the variable to be evaluated

$(= expression) forces the expression to be evaluated.

It may be a bit belt-and-braces but it does work.

I always type variables within $() as it seems to work consistently, whereas just typing the variable name does not always evaluate correctly

Re: How to use variable in set analysis ?

sunny_talwar — Thu, 05 Mar 2015 14:27:23 GMT

Great, its good to know that it does work. I will probably try to find its use somewhere.

Best,

Re: How to use variable in set analysis ?

Thu, 05 Mar 2015 14:29:47 GMT

This works ! Thank you !

Now, I would like to understand what does the equal sign do ? What does "evaluate globally" mean ? I I don't put the equal sign, it is "locally" evaluated ?

Re: How to use variable in set analysis ?

luciancotea — Thu, 05 Mar 2015 14:36:34 GMT

http://community.qlik.com/blogs/qlikviewdesignblog/2014/11/24/the-equal-sign

Re: How to use variable in set analysis ?

jonathandienst — Thu, 05 Mar 2015 14:38:34 GMT

Without the = sign, you are inserting the expression and the expression is evaluated after expansion. That's why you use =$(var) in the text box. The expression is expanded and then evaluated.

With the = sign, the variable is evaluated continuously and the value itself is inserted. Now you can use

=var in the text box, because var has already been calculated.

HTH

Jonathan