topic Re: Sets when NOT equal to in QlikView

Sets when NOT equal to

Wed, 06 Jul 2011 11:05:25 GMT

=sum({$<Activity = {"*Funding Sheet*"}>}count(Opics)/2)

Hi Guys

I have the above expression, but I wish it to read , when Activity is NOT equal to Funding sheet. I have tried <> and NOT but these dont work .

Can anyone help ?

Or alternatively is it possible to set 2 sets,

So Activity is equal to either Structure or Unstructure ?

Thanks

Re: Sets when NOT equal to

Miguel_Angel_Baeyens — Wed, 06 Jul 2011 11:08:44 GMT

Hi,

You are very close. Use this instead

sum({$<Activity -= {"*Funding Sheet*"}>}count(Opics)/2)

(note the "-" sign that makes the "-=" operator).

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Re: Sets when NOT equal to

Wed, 06 Jul 2011 11:12:03 GMT

Thanks Miguel

Sets when NOT equal to

Wed, 06 Jul 2011 12:15:02 GMT

Hi Miguel,

If I wanted the set to be

Not equal to Funding sheet and where AM = Anne

Can I have 2 conditions in set ?

=count({$<Activity -= {"*Funding Sheet*"}>},{$<[AM]={"*Anne*"}>}(Opics))

I have tried above, but its clearly incorrect !!

Thanks

Sets when NOT equal to

Wed, 06 Jul 2011 13:03:02 GMT

Try out this below expression.

count({$<Activity -= {"*Funding Sheet*"},[AM] = {"*Anne*"} >} Opics)

-Sridhar

Re: Sets when NOT equal to

Miguel_Angel_Baeyens — Wed, 06 Jul 2011 16:38:43 GMT

Hi,

Shridar's answer is the correct one for your second question.

Anyway, I'd like to note the meaning of quoting in set analysis, it's important since, as in every other condition, speed matters, and the faster the set is build the faster your chart will render.

No quotes are used when the values you want to use in your set modifiers are numeric, for example, the year

Sum({< Year = {2010, 2011} >} Sales)

Single quotes are used with literals (strings), for example, a list of fixed values

Sum({< Country = {'ES', 'FR', 'UK'} >} Sales)

Double quotes are used to search for the values that match certain pattern

Sum({< Customer = {"AA*"} >} Sales)

Double quotes are used to enclose single quotes as well, and to get sets depending on functions, for example, customers with Sales above 1000

Sum({< Customer = {"=Sum(Sales) > 1000"} >} Sales)

So applied to the above, with fixed values, the expression should look like (although the one posted will work)

Count({< Activity -= {'Funding Sheet'}, AM = {'Anne'} >} Opics)

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Re: Sets when NOT equal to

Thu, 07 Jul 2011 07:19:07 GMT

Hi Miguel

That actually clears up alot of questions that I had about sets

Thanks

Anne