Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
I agree, set analysis syntax could be a bit tricky. But there are some basic guidelines besides the HELP to help you get started:
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.
Instead of <> use the E() function.
I agree, set analysis syntax could be a bit tricky. But there are some basic guidelines besides the HELP to help you get started:
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.
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.
count({<CLAIMCANCELLATINREASON-={'Created in Error'}>}CLAIMID)
or
count({<CLAIMCANCELLATINREASON ={'*'}-{'Created in Error'}>}CLAIMID)
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
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
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)
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]))
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