topic Re: expression error in SET Analysis with variable in QlikView

expression error in SET Analysis with variable

Fri, 24 Oct 2014 07:51:38 GMT

I´m having a syntaxproblem with this Set Analysis

count({<AAR=vCurrentyear,Gruppe={'Tyveri fra borger'} >}JOURNALNR)

the problem lies at the comma after the vCurrentYear

I tried

count({<AAR={vCurrentyear},Gruppe={'Tyveri fra borger'} >}JOURNALNR)

but it doesnt work

Re: expression error in SET Analysis with variable

alexandros17 — Fri, 24 Oct 2014 07:56:27 GMT

try this

count({<AAR={$(vCurrentyear)},Gruppe={'Tyveri fra borger'} >}JOURNALNR)

let me know

Re: expression error in SET Analysis with variable

maleksafa — Fri, 24 Oct 2014 08:03:19 GMT

you need to add the $ sign before the variable in order to evaluate it, use $(vCurrentYear)

Re: expression error in SET Analysis with variable

Colin-Albert — Fri, 24 Oct 2014 08:07:18 GMT

In set analysis expression modifiers, the field is always compared to a set of results, so the values after the = sign must be enclosed in parenthesis { }. Also variables should be enclosed in $() to ensure they are evaluated.

If vCurrentyear is text then the expression must be enclosed in quotes.

If vCurrentyear is numeric use count({<AAR={$(vCurrentyear)}, Gruppe={'Tyveri fra borger'} >} JOURNALNR)

If vCurrentyear is text use count({<AAR={'$(vCurrentyear)'}, Gruppe={'Tyveri fra borger'} >} JOURNALNR)

Re: expression error in SET Analysis with variable

Fri, 24 Oct 2014 08:09:55 GMT

Hi Alessandro

It doent work - it says expression ok, but I dont get a result only a -

Re: expression error in SET Analysis with variable

Colin-Albert — Fri, 24 Oct 2014 08:11:56 GMT

Is vCurrentyear holding the data as text not numeric values?

Try adding quotes around the variable.

count({<AAR={'$(vCurrentyear)'}, Gruppe={'Tyveri fra borger'} >} JOURNALNR)

Re: expression error in SET Analysis with variable

Fri, 24 Oct 2014 08:16:31 GMT

Hi Colin

using yours gives me zeroes

Re: expression error in SET Analysis with variable

Fri, 24 Oct 2014 08:17:27 GMT

the variable vCurrentyear is made like this =year(today())

Re: expression error in SET Analysis with variable

Colin-Albert — Fri, 24 Oct 2014 08:20:19 GMT

For the set expression to work, both the AAR data and vCurrentyear must hold the same values to match.

Can you post a screenshot of a listbox showing some AAR values.

Also the case of the Gruppe text must exactly match 'Tyveri fra borger'

Re: expression error in SET Analysis with variable

Fri, 24 Oct 2014 08:27:00 GMT

Hi Colin

Of course you are right

The AAR(means year in danish) is not made from a date, but is a text field.

If I use an expression like this

count(if(AAR=vCurrentYear,JOURNALNR))

it works fine

Re: expression error in SET Analysis with variable

Fri, 24 Oct 2014 08:56:27 GMT

I dont have an image om the field but it contains this list

2010

2011

2012

2013

2014

Re: expression error in SET Analysis with variable

Fri, 24 Oct 2014 09:02:34 GMT

Hi Colin

the Data in the field AAR comes from a SQl database and is formatted as an INT

Re: expression error in SET Analysis with variable

Colin-Albert — Fri, 24 Oct 2014 09:04:35 GMT

An image would help as it makes a difference if the list box shows the data as left or right justified.

If the listbox shows

2010

2011

2012

2013

then the data is text.

If the listbox shows

2010

2011

2012

2013
then the data is numeric

Re: expression error in SET Analysis with variable

Fri, 24 Oct 2014 09:07:01 GMT

the data is numeric

Re: expression error in SET Analysis with variable

Colin-Albert — Fri, 24 Oct 2014 09:09:38 GMT

Do you get data returned from this expression?

count( {<AAR={$(vCurrentyear)} >} JOURNALNR)

If not, add a textbox showing =$(vCurrentyear) to see that that contains the value you expect. 2014

Re: expression error in SET Analysis with variable

Fri, 24 Oct 2014 09:18:35 GMT

yes

Re: expression error in SET Analysis with variable

Fri, 24 Oct 2014 09:25:07 GMT

the textbox shows 2014

Re: expression error in SET Analysis with variable

Colin-Albert — Fri, 24 Oct 2014 10:03:54 GMT

Can you add a list box on the Gruppe field and confirm that the value 'Tyveri fra borger' exists

Re: expression error in SET Analysis with variable

Fri, 24 Oct 2014 10:05:43 GMT

yes

Re: expression error in SET Analysis with variable

Colin-Albert — Fri, 24 Oct 2014 10:30:59 GMT

Hi Soren,

>>using yours gives me zeroes

Oops! I missed this reply earlier - the zero means that the set expression is correct, but there are no matching JOURNALNR values to count, hence the value is zero.

Do you get values returned for the expression count({1 <AAR={'$(vCurrentyear)'}, Gruppe={'Tyveri fra borger'} >} JOURNALNR)

The 1 will cause the expression to ignore all other data selections. If you get data with this set, then view your current selections to identify what values are being filtered in your data.

If the 1 set expression above returns zero, then your data has no Journals for AAR=2014 and Gruppe = 'Tyveri fra borger'