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

            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

              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

                    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