Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Highlighted

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

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

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.

6 Replies
SunilChauhan
Esteemed Contributor

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

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

Not applicable

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

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

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

MVP
MVP

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

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

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

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

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

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