topic Re: How to specify explicit > and < in set modifier in QlikView

How to specify explicit > and < in set modifier

Peter_Cammaert — Tue, 11 Mar 2014 15:32:10 GMT

Is there a method to specify '>' and '<' characters in set modifiers as part of explicit diminsion valiues and block QlikView from treating them as numeric/date range specificers? For example I have an OpenInvoice dimension that contains - amongst others - the value '>90d'. Using that one in a set modifier won't produce the expected results.

I know I can work around this problem by either:

using an inverse modifier: specifying the values to exclude. That won't work if the inverse set als contains dimension values with a < or >.
treating all dimension values with the dual() function and using the numerical value in a set modifier. That won't work well if there are too many different value ranges to exclude/include.
creating a dimension value ID field without > or < that corresponds 1:1 to the different texts, and use that value in a set modifier. I will use a hack like this if there aren't any other solutions.

There must be another, more elegant way to solve this. Any suggestions?

Re: How to specify explicit > and < in set modifier

SunilChauhan — Tue, 11 Mar 2014 15:46:38 GMT

see below where > and < are used

sum({Year={">$(=Year-3) <$(Year)"}} Amountfield)

for Excluding values you can use below (- Operator)

sum({Year - ={">$(=Year-3) "}} Amountfield)

hope this helps

Re: How to specify explicit > and < in set modifier

Tue, 11 Mar 2014 15:48:52 GMT

Peter,

I use double quotes:

= sum({<ID={"<3", ">10"}>} Value)

If I use a variable that contain the value, i also use the double quotes "$(Myvar)"

Perhaps, I have not understood completely your point.

Fabrice

Re: How to specify explicit > and < in set modifier

Peter_Cammaert — Tue, 11 Mar 2014 16:32:43 GMT

Hey guys,

I do NOT want the behavior you're explaining to me. I want no default range behavior.

If I specify

...{<[Invoice Cat] = {">90d"}>} ...

then I want to apply my aggregation to all records that have explicit value ">90d" in field [Invoice Cat]. How can I explain that to QlikView?

Sorry for not being clearer...

Re: How to specify explicit > and < in set modifier

maxgro — Tue, 11 Mar 2014 21:53:57 GMT

hope to understand

Tab1:

load rowno() as id, *;

LOAD * INLINE [

Inv

>90d

];

count({$ <Inv={'>90d'}>} Inv)

count({$ <Inv={'>90d','>2'}>} Inv)

Re: How to specify explicit > and < in set modifier

Peter_Cammaert — Wed, 12 Mar 2014 09:53:48 GMT

Massimo is right about using single quotes for literal interpretation of strings. But unfortuantely, that didn't help me as I tested that trick already and it still didn't get me more than 0.

I think I found out what was causing the anomaly. When creating the Overdue category for unpaid invoices, I used a dual() function to attach a sorting order to the category, e.g. Not Expired = dual('NE', 0), Less than 31 days overdue = dual('0-30d', 1) and so on.

Until I get to More than 90 days overdue = dual('>90d', 4) and this one causes trouble when later on I use a text box expression like:

=sum( {<ExpCat={'>90d'}>} Amount)

QlikView doesn't know how to handle that one. A bug? A documented feature? Don't know. I dropped all the dual() stuff and handled the sort order using another technique. Now it's working as expected.

Thanks for helping.

Re: How to specify explicit > and < in set modifier

Wed, 12 Mar 2014 10:08:29 GMT

Peter,

1) To my experience, in the set analysis, you can use single or double quotes. Even quare brackets.

I do not think the type of quotes makes any difference (but in fact there is perhaps one that I did not understand)

2) The dual() function was the origin of your problem. It is why it was working with my test db, and could not understand the point. => I have to think how the field is stored.

Fabrice