Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Why is that so difficult to use <,>,<> in set analysis?

I'm trying to filter a number of claims but using set analysis formulas. I have read many comments in Qlikvew forum. There is not a definite answer how to use <>,>,<. There should be a simple way to use them. It will be disappointing if not.

What I want is to count the number of claims except the ones that are created in error.

I though this should work Count({$<ClaimCancellationReason<>{'Created In Error'}>}ClaimId) but it didn't. Count({$<ClaimCancellationReason={"<>$('Created In Error')"}>}ClaimId) didn't work either.

I have some difficulties to use > and < as well. For instance Count({$<CreatedDate>{'01/06/2015'}>}ClaimId).

I tried count({$<CreatedDate={">{'01/06/2015'}>}ClaimId) didn't work.

Thank you,

Sara

1 Solution

Accepted Solutions
MVP
MVP

Re: Why is that so difficult to use <,>,<> in set analysis?

I agree, set analysis syntax could be a bit tricky. But there are some basic guidelines besides the HELP to help you get started:

A Primer on Set Analysis

Dates in Set Analysis

Note that the set modifiers are always constructed like

< FIELD = SET >

i.e. there is always an equal sign after the FIELD name (and it must be a field name, no expression etc.). The equal sign is more like an assignment operator, not a comparison operator.

The SET could be an explicite set defined like

{1,2,3,6, 'A'}

or a search in field values {"..."}, or using functions p() or e() or reference another field.

So, if you want to exclude one value from counting, try


=Count({$<ClaimCancellationReason -= {'Created In Error'} >} ClaimId)


where -= is a abbreviation to tell QV to remove the element {'Created In Error'} from the set.

12 Replies
jerrysvensson
Valued Contributor II

Re: Why is that so difficult to use <,>,<> in set analysis?

Instead of <> use the E() function.

MVP
MVP

Re: Why is that so difficult to use <,>,<> in set analysis?

I agree, set analysis syntax could be a bit tricky. But there are some basic guidelines besides the HELP to help you get started:

A Primer on Set Analysis

Dates in Set Analysis

Note that the set modifiers are always constructed like

< FIELD = SET >

i.e. there is always an equal sign after the FIELD name (and it must be a field name, no expression etc.). The equal sign is more like an assignment operator, not a comparison operator.

The SET could be an explicite set defined like

{1,2,3,6, 'A'}

or a search in field values {"..."}, or using functions p() or e() or reference another field.

So, if you want to exclude one value from counting, try


=Count({$<ClaimCancellationReason -= {'Created In Error'} >} ClaimId)


where -= is a abbreviation to tell QV to remove the element {'Created In Error'} from the set.

dgreenberg
Contributor III

Re: Why is that so difficult to use <,>,<> in set analysis?


I recommend this website http://tools.qlikblog.at/SetAnalysisWizard/QlikView-SetAnalysis_Wizard_and_Generator.aspx?sa=

It's not perfect but helps with a lot of set analysis functionality.

admin1
New Contributor III

Re: Why is that so difficult to use <,>,<> in set analysis?

count({<CLAIMCANCELLATINREASON-={'Created in Error'}>}CLAIMID)

                                                 or

count({<CLAIMCANCELLATINREASON ={'*'}-{'Created in Error'}>}CLAIMID)

Re: Why is that so difficult to use <,>,<> in set analysis?

The mistake in your assumption may become clear when you realize that a set analysis does not consist of comparisons. It consists entirely of assignments. And first you have to define a collection of values to be assigned. The specification

{<MyField = {1,5,10}>}

actually means: temporarily put the set of values 1,5,10 in an imaginary MyField listbox. In set analysis, you always define a set of (temporarily) selected values and (again temporarily) assign it to a particular field.

{<MyField <> {1,5,10}>}

wouldn't work because set analysis code wouldn't know what to (temporarily) assign to Myfield. An alternative like

{<MyField = {'<10'}>}


however will work, because first the set of values is defined as "every value from Myfield you can find that is smaller than 10", and only then this set of new values is assigned to/selected in MyField.


The last type of set definition is called a "search" or an "advanced search". It is a very powerful technique that corresponds to the stuff you can enter in a search box that pops up when you start typing in an active list box.


Peter

Re: Why is that so difficult to use <,>,<> in set analysis?

Oh and BTW instead of count({$<CreatedDate={">{'01/06/2015'}>}, try

count({$<CreatedDate={'>01/06/2015'}>}


because this expression will work in the seach field of listbox CreatedDate too.


Best,


Peter

neetha_p
Honored Contributor

Re: Why is that so difficult to use <,>,<> in set analysis?

Hi Sara,


E() - excludes subset it set analysis

-= not equal to operator in set analysis


=Count({$<ClaimCancellationReason -= {"$(=[Created In Error])"}>}ClaimId)

=Count({$<ClaimCancellationReason = E({"$(=[Created In Error])"})>}ClaimId)

=Count({$<CreatedDate = {">$(=date(Max(CreatedDate)))"}>}  ClaimId)


Not applicable

Re: Why is that so difficult to use <,>,<> in set analysis?

I agree the syntax with any expression other than equal can be difficult and usually involves ' or "

another option is to define a variable that has your calculated result and then you use set analysis against the variable

example

Sum({$< [Earliest Date] = {"$(vOrderDateRange)"} >} [Sales])

vOrderDateRange)"} is defined as

=If(GetSelectedCount([Earliest Date]) <> 0, '>=' & Min([Earliest Date]) & '<=' & Max([Earliest Date]), '>=' & AddMonths(Max([Earliest Date]), -12) & '<=' & Max([Earliest Date]))

Not applicable

Re: Why is that so difficult to use <,>,<> in set analysis?

Thank you for your reply.

Now there is another issue. The problem is that it doesn't count the claims that their ClaimCancellationReason column is blank. It only counts the claims that are cancelled for other reason and not new, open or pending claims.

I need to show all claim except those ones that are created in error.

Thank you,

Sara