topic Re: Re: Use of variables in Set Analysis in QlikView

Use of variables in Set Analysis

Mon, 20 Jan 2014 16:56:48 GMT

I feel I'm missing something very obvious here. Can someone please tell me the difference between these two expressions?

This expression returns data:

Count({<MATTER.MATTER_OPEN_YEAR={'2014'}>} MATTER.CLNT_MATT_CODE)

This expression does not. The currentYear variable contains the value '2014', which I have confirmed by displaying the contents in a text box. The expression editor shows no errors.

Count({<MATTER.MATTER_OPEN_YEAR={$(currentYear)}>} MATTER.CLNT_MATT_CODE)

Many thanks, Gavin

Re: Use of variables in Set Analysis

santharubban — Mon, 20 Jan 2014 17:04:12 GMT

Hi Gavin,

Try this

Count({<MATTER.MATTER_OPEN_YEAR={"$(currentYear)"}>} MATTER.CLNT_MATT_CODE)

Re: Use of variables in Set Analysis

alexandros17 — Mon, 20 Jan 2014 17:04:30 GMT

At first verify with a text object that your variable really contains the correct value, then is so, try

Count({<MATTER.MATTER_OPEN_YEAR={'$(currentYear)'}>} MATTER.CLNT_MATT_CODE)

Just add ' because expressions are equal

Hope this is helpfull

Alexandros

Re: Use of variables in Set Analysis

Mon, 20 Jan 2014 17:13:00 GMT

Thank you both for the answers. Unfortunately no luck.

I have verified via text box that the variable contains '2014'. (That variable was itself derived from another variable using Left(currentPeriod, 4), where the currentPeriod is '201410'...so there isn't much scope for error...).

Re: Re: Use of variables in Set Analysis

Mon, 20 Jan 2014 17:32:23 GMT

Hey Gavin,

Please check out the attached file. Hope it helps. I created a variable and used it in the Set Analysis similar to what you are doing.

Thanks

Re: Use of variables in Set Analysis

Mon, 20 Jan 2014 17:52:07 GMT

Gavin,

OK your variable contains 2014.

But did you add the quotes ? if the basic syntax, without any variable is '2014' you must do '$(variable)' => put quotes

Fabrice

Re: Use of variables in Set Analysis

Anonymous — Mon, 20 Jan 2014 18:16:26 GMT

Gavin,

Can you upload your application? As Ajay Prabhakaran examples shows, you can use variable without quotes, and it works also with single and with double quotes. The problem is somewhere else... Maybe you misspell the variable? It is case sensitive.

Regards,

Michael

Re: Use of variables in Set Analysis

Tue, 21 Jan 2014 08:56:46 GMT

Thank you Ajay - That looks like an accurate representation of what I'm trying to do...but my file seems to behave differently. My QV file is too large to attach, but I will attach screenshots to a later post below.

Gavin

Re: Use of variables in Set Analysis

Tue, 21 Jan 2014 08:58:24 GMT

Hi Aunez - I have tried adding quotes but have the same problem, i.e.

This works: Count({<MATTER.MATTER_OPEN_YEAR={'2014'}>} MATTER.CLNT_MATT_CODE)

This does not: Count({<MATTER.MATTER_OPEN_YEAR={'$(currentYear)'}>} MATTER.CLNT_MATT_CODE)

Thanks, Gavin

Re: Use of variables in Set Analysis

alexandros17 — Tue, 21 Jan 2014 09:05:21 GMT

Can you try this:

Add a new expression and write : if($(currentYear)=2014, 'Y', 'N')

Let me know what returns this expression

I expect Y....

Re: Use of variables in Set Analysis

Tue, 21 Jan 2014 09:08:46 GMT

well, I would use the equal sign + double quotes. Sth like (but not tested):

Count({<MATTER.MATTER_OPEN_YEAR={“= $(currentYear)”}>} MATTER.CLNT_MATT_CODE)

Fabrice

Re: Use of variables in Set Analysis

Tue, 21 Jan 2014 09:11:33 GMT

Hi Michael,

Unfortunately the file is very large - I'm not sure if the screenshots below will help?

currentPeriod variable:

currentYear variable:

textbox to verify:

Chart + formula with hardcoded '2014':

Chart + formula with currentYear variable (have tried with single, double and no quotes):

Thanks, Gavin

Re: Use of variables in Set Analysis

Tue, 21 Jan 2014 09:14:57 GMT

Hi Alessandro - That returns a 'Y':

(Please also see the just-attached screenshots above)

Thanks, Gavin

Re: Use of variables in Set Analysis

Tue, 21 Jan 2014 09:20:47 GMT

Thank you Aunez. The use of double quotes, plus the extra = sign within them, works, i.e.:

Count({<MATTER.MATTER_OPEN_YEAR={"=$(currentYear)"}>} MATTER.CLNT_MATT_CODE)

Gavin

Re: Use of variables in Set Analysis

Anonymous — Tue, 21 Jan 2014 11:28:18 GMT

Gavin,

Explanation of the problem and the solution:

Your variable content is not '2014' - it is expression 'left($(currentPeriod),4)'. The text box to verify had to be
=currentYear

That's why the solution from AUNEZ FABRICE resolved the problem by adding "=" before the variable.

Regards,

Michael

PS: Regarding file upload - you can always use "data reduction" and scrambling.