topic Trouble Using a Variable in Set Analysis in QlikView

Trouble Using a Variable in Set Analysis

Sat, 29 Aug 2009 04:07:58 GMT

I have created a variable "vSelection" in my QVW, which is currently set to "USMC." Using a text box with the expression =$(vSelection), USMC appears as expected. The trouble comes when using the variable in set analysis.

This set analysis expression will work:

TextCount ({<FIELD_1= {USMC}>}FIELD_2)

But this one won't, even though I've confirmed above that $(vSelection) = USMC

TextCount ({<FIELD_1= {$(vSelection)}>}FIELD_2)

Any thoughts?

Trouble Using a Variable in Set Analysis

Sat, 29 Aug 2009 06:56:27 GMT

I tried this in 8.5 SR5, and it worked beautifully.

Are you using V9?

The other thing you can try is putting quotes around the variable expansion, e.g.

TextCount ({<FIELD_1= {'$(vSelection)'}>}FIELD_2)

This will handle values with spaces correctly, although you should not need this for your specific example.

Phil

Trouble Using a Variable in Set Analysis

Wed, 02 Sep 2009 01:42:14 GMT

I am working in 8.5. Unfortunately, the fix you suggest doesn't work. Any other idea?

Trouble Using a Variable in Set Analysis

Wed, 02 Sep 2009 02:05:39 GMT

try with

TextCount ({<FIELD_1= {$(=vSelection)}>}FIELD_2)

Trouble Using a Variable in Set Analysis

Wed, 02 Sep 2009 03:49:10 GMT

Still nothing. Thanks for the effort. Any other syntax tweaks to try?

Trouble Using a Variable in Set Analysis

Wed, 02 Sep 2009 04:13:34 GMT

This sounds like a bug to me. Can you provide the exact build or SR you are using, e.g. 8.50 SR4?

You could also try posting the document, and I would take a look at it for you. You should either remove (reduce data) or scramble the data prior to posting - or if you can create a sample, that would be helpful.

Phil

Trouble Using a Variable in Set Analysis

Thu, 03 Sep 2009 02:48:54 GMT

Hi Phil,

The build info is: Client Build Number 8.50.6261.0409.2

As far as scrambling the data, I can do that to help with investigation if it is indeed a bug. Is there an easy way to scramble things in QV? Thanks for your interest in solving my problem.

Trouble Using a Variable in Set Analysis

Thu, 03 Sep 2009 03:10:27 GMT

You may want to try a combination of two of these tips: including the equals sign and using single quotes.

I would try:

TextCount ({<FIELD_1= {'$(=vSelection)'}>}FIELD_2)

Also, I seem to recall some scrambling functionality in 8.5. I don't remember exactly how it worked. EDIT: It looks like it is the same as 9. Under document properties, there is a Scramble tab, which can be used to scramble certain fields. See section 1.13 of Book II of the Reference Manual.

Trouble Using a Variable in Set Analysis

prieper — Thu, 03 Sep 2009 03:13:44 GMT

Hi,

Settings -> Document Properties -> Scrambling and then choose the fields to scramble.

Subject to size of the application it might make sense to reduce your data with filtering a few records and then under File -> Reduce Data -> Keep Possible Values and save your file

HTH

Peter

Trouble Using a Variable in Set Analysis

Wed, 08 Sep 2010 09:31:49 GMT

One critical thing to remember when using a variable in set analysis is to ignore the selections which the user may make which influences the behaviour of the variable.

For example,

User makes selections on Year, and Month fields.

In your set analysis you want to create a formula to sum the previous month sales. If a user selects July and 2010, the vLM of June 2010, is not available in the SET. That is, unless you ignore the Year and Month selections.

Instead of the formula:

Sum({$<Period = {'$(vLM)'}>} Sales)

You may need to write:

Sum({$<Year,Month,Period = {'$(vLM)'}>} Sales)

This is relevant for a connected date calendar and not a date island.