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: 
Peter_Cammaert
Partner - Champion III
Partner - Champion III

How to specify explicit > and < in set modifier

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?

1 Solution

Accepted Solutions
Peter_Cammaert
Partner - Champion III
Partner - Champion III
Author

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.

View solution in original post

6 Replies
SunilChauhan
Champion
Champion

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

Sunil Chauhan
Not applicable

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

Peter_Cammaert
Partner - Champion III
Partner - Champion III
Author

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...

maxgro
MVP
MVP

hope to understand

Tab1:

load rowno() as id, *;

LOAD * INLINE [

    Inv

    1

    >2

    3

    >4

    >90d

    5

    1

    3

    5

    >90d

];

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

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

2014-03-11 22_52_42-Clipboard.png

Peter_Cammaert
Partner - Champion III
Partner - Champion III
Author

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.

Not applicable

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