Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

How to use variable in set analysis ?

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

1 Solution

Accepted Solutions
MVP
MVP

Re: How to use variable in set analysis ?

Hi

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


Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
12 Replies
datanibbler
Esteemed Contributor

Re: How to use variable in set analysis ?

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 ?

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,

S

chrislofthouse
Contributor

Re: How to use variable in set analysis ?

Try this:

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

luciancotea
Valued Contributor

Re: How to use variable in set analysis ?

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

Re: How to use variable in set analysis ?

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

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

MVP
MVP

Re: How to use variable in set analysis ?

Hi

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


Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

Re: How to use variable in set analysis ?

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

Best,

S

Re: How to use variable in set analysis ?

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 ?

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

Best,

S

Community Browser