Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
swuehl
MVP
MVP

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.

View solution in original post

12 Replies
jerrysvensson
Partner - Specialist II
Partner - Specialist II

Instead of <> use the E() function.

swuehl
MVP
MVP

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
Luminary Alumni
Luminary Alumni


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.

Anonymous
Not applicable
Author

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

                                                 or

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

Peter_Cammaert
Partner - Champion III
Partner - Champion III

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

Peter_Cammaert
Partner - Champion III
Partner - Champion III

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

Anonymous
Not applicable
Author

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
Author

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
Author

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