6 Replies Latest reply: Mar 12, 2014 6:08 AM by Fabrice Aunez RSS

    How to specify explicit > and < in set modifier

    Peter Cammaert

      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
          Sunil Chauhan

          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



            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.



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

              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
                Peter Cammaert

                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



                    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.