5 Replies Latest reply: Jun 2, 2017 9:04 AM by Wendy Harrison RSS

    Scaled Values Filter - Qlik Sense

    Wendy Harrison

      Hi Everyone,

       

      I was wondering if anyone could help please.

       

      I need to create a filter which will allow me to filter my values on a table.

       

      I have an average cost which is calculated with the following expression

       

      (sum ({<DocType= {'InvoiceID'}>}Overall)- sum ({<DocType= {'[Credit ID]'}>}Overall)) / (Sum (Count))

       

      from the result of this expression I need to create a filter for the following values.

       

      Between       £0 - £500

      Between £1000 - £2000

      Between £2000 - £3000

      Between £3000 - £4000

      Between £4000 - £5000

      Between £5000 - £7000

      Between £7000 - £9000

      Anything above £9000

       

      I am just not quite sure how I go about this.

       

      Any help would be much appreciated.

       

      Many Thanks

      Kind Regards

      Wendy

        • Re: Scaled Values Filter - Qlik Sense
          bruno bertels

          Hi Wendy

           

          Have a look to this post it will help you

           

          https://community.qlik.com/message/1269003#1269003

           

          You have 2 way of doing your requierment :

          Using Class() function but as i see that you do not use the same scale : 500 , or 1000 or  2000 , class function will not be the solution in your case

           

          Then use bucket :

           

          If(

          (sum ({<DocType= {'InvoiceID'}>}Overall)- sum ({<DocType= {'[Credit ID]'}>}Overall)) / (Sum (Count)) < 500,

          'Between       £0 - £500',

          if(

          (sum ({<DocType= {'InvoiceID'}>}Overall)- sum ({<DocType= {'[Credit ID]'}>}Overall)) / (Sum (Count)) < 1000,

          'Between       £500 - £1000',

          if(

          (sum ({<DocType= {'InvoiceID'}>}Overall)- sum ({<DocType= {'[Credit ID]'}>}Overall)) / (Sum (Count)) < 2000,

          'Between       £1000- £2000',

          if(

          (sum ({<DocType= {'InvoiceID'}>}Overall)- sum ({<DocType= {'[Credit ID]'}>}Overall)) / (Sum (Count)) < 3000,

          'Between       £2000- £3000,

          if(

          (sum ({<DocType= {'InvoiceID'}>}Overall)- sum ({<DocType= {'[Credit ID]'}>}Overall)) / (Sum (Count)) < 4000,

          'Between       £3000- £4000,

          if(

          (sum ({<DocType= {'InvoiceID'}>}Overall)- sum ({<DocType= {'[Credit ID]'}>}Overall)) / (Sum (Count)) < 5000,

          'Between       £4000- £5000,

          if(

          (sum ({<DocType= {'InvoiceID'}>}Overall)- sum ({<DocType= {'[Credit ID]'}>}Overall)) / (Sum (Count)) < 7000,

          'Between       £5000- £7000,

          if(

          (sum ({<DocType= {'InvoiceID'}>}Overall)- sum ({<DocType= {'[Credit ID]'}>}Overall)) / (Sum (Count)) < 9000,

          'Between       £7000- £9000',

          'Anything above £9000'

          ))))))))

           

          regards

           

          Bruno

            • Re: Scaled Values Filter - Qlik Sense
              Wendy Harrison

              Thanks Bruno,

               

              This is great but please could you help me into where I put it to create a Filter in Qlik Sense.

               

              I have tried to put it into my load script but my script is very intense so I am struggling to know where in the script to put it.  Can I add it somewhere in an expression?

               

              Many Thanks

              Kind Regards

              Wendy

                • Re: Scaled Values Filter - Qlik Sense
                  bruno bertels

                  Hi Wendy

                   

                  As your mesure is a set analysis , you can't add it in the script and create a dimension .

                   

                  but in backend you can do it like this :

                   

                  Select Graph :

                   

                  then Filter panel :

                   

                   

                  Clik on the FX menu :

                   

                   

                   

                  Add your mesure :

                   

                  If(

                  (sum ({<DocType= {'InvoiceID'}>}Overall)- sum ({<DocType= {'[Credit ID]'}>}Overall)) / (Sum (Count))< 500,

                  'Between       £0 - £500',

                  if(

                  (sum ({<DocType= {'InvoiceID'}>}Overall)- sum ({<DocType= {'[Credit ID]'}>}Overall)) / (Sum (Count))< 1000,

                  'Between       £500 - £1000',

                  if(

                  (sum ({<DocType= {'InvoiceID'}>}Overall)- sum ({<DocType= {'[Credit ID]'}>}Overall)) / (Sum (Count))< 2000,

                  'Between       £1000- £2000',

                  if(

                  (sum ({<DocType= {'InvoiceID'}>}Overall)- sum ({<DocType= {'[Credit ID]'}>}Overall)) / (Sum (Count))< 3000,

                  'Between       £2000- £3000',

                  if(

                  (sum ({<DocType= {'InvoiceID'}>}Overall)- sum ({<DocType= {'[Credit ID]'}>}Overall)) / (Sum (Count))< 4000,

                  'Between       £3000- £4000',

                  if(

                  (sum ({<DocType= {'InvoiceID'}>}Overall)- sum ({<DocType= {'[Credit ID]'}>}Overall)) / (Sum (Count))< 5000,

                  'Between       £4000- £5000',

                  if(

                  (sum ({<DocType= {'InvoiceID'}>}Overall)- sum ({<DocType= {'[Credit ID]'}>}Overall)) / (Sum (Count))< 7000,

                  'Between       £5000- £7000',

                  if(

                  (sum ({<DocType= {'InvoiceID'}>}Overall)- sum ({<DocType= {'[Credit ID]'}>}Overall)) / (Sum (Count))< 9000,

                  'Between       £7000- £9000',

                  'Anything above £9000'

                  ))))))))

                   

                  Name your filter pane

                   

                  Regards

                   

                  Bruno

              • Re: Scaled Values Filter - Qlik Sense
                beck bakytbek

                Hi Wendy,

                 

                for you filter, look at this blog,here i used buckets:

                 

                https://community.qlik.com/thread/257523

                 

                i hope that helps

                 

                Beck