Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Using variable in Set Analysis

Currently I'm having a problem to use variables in Set Analysis.

I have set them as below to have 3 variables representing past 3 months.

Set vPastMonth1 = num(Month(AddMonths(Date(Today()),-1)));

Set vPastMonth2 = num(Month(AddMonths(Date(Today()),-2)));

Set vPastMonth3 = num(Month(AddMonths(Date(Today()),-3)));

And then I'm using them in an expression to get the maximum percentage with past 3 months of 2015:

max({$ <RawMonth={$(vPastMonth1),$(vPastMonth2),$(vPastMonth3)}, RawYear = {2015}>} [Percentage])

However, it won't count. If I set them to constant like 12,11,10, this expression will work. Further checked the set statement by using a text box, and they are correct. May I know how come they won't work with set analysis? Thank you very much.

5 Replies
Honored Contributor II

Re: Using variable in Set Analysis

Hi Felix,

Try this:

max({$ <RawMonth = { =$(vPastMonth1), =$(vPastMonth2), =$(vPastMonth3)}, RawYear = {2015}>} [Percentage])

Esteemed Contributor

Re: Using variable in Set Analysis

may be this?

=max({<RawMonth={'=$(=vPastMonth1),=$(=vPastMonth2),=$(=vPastMonth3)'},RawYear = {'2015'}>} [Percentage])

Valued Contributor

Re: Using variable in Set Analysis


may be this :

max({<RawMonth={'=$(=vPastMonth1)','=$(=vPastMonth2)','=$(=vPastMonth3)'},RawYear = {'2015'}>} [Percentage])

Valued Contributor III

Re: Using variable in Set Analysis

Try this out as well:

max({$ <RawMonth={'$(vPastMonth1)','$(vPastMonth2)','$(vPastMonth3)'}, RawYear = {2015}>} [Percentage])

Not applicable

Re: Using variable in Set Analysis

Thanks guy!! You guys are really nice!!!

However, only the one from Bruno Bertels is returning a value, but it seems like it's returning the max of the whole year instead of Oct, Nov and Dec only. I wish this can be past 3 months from today. Interestingly, in this formula, vPastMonth variables are not changing the color. Does this mean Qlik isn't recognizing them? But it's returning a value.

I believe it should be a string/integer issue. The thing passing into the set analysis expression has to be integer but somehow it's not recognizing well with variables built by formulas.

Still trying on this but no luck. Any idea? Thank you very much.