5 Replies Latest reply: Jul 31, 2012 7:05 AM by walkdvqv RSS

    Display records based on value

      Hi,

      I have a straight table where I only want to display records that have a calculated variance of over 10% or under -10%.

      With dimension limits you can do one or the other but not both which is what I need.

      Does anyone have any ideas?

       

      Thanks.

        • Re: Display records based on value

          post data sample please

            • Re: Display records based on value

              Hi,

              So I have a list of Customers with their costs and planned costs from which I have calculated a % variance.

              In my table I only want to show the outliers - those customers with a variance of over 10% or under -10%. So using the example below, I only want to display the underlined records.

               

              Customer          Cost          Planned         %Variance

              Customer A          17               20                    15%

              Customer B          20               21                      5%

              Customer C          32               37                    -13%

              Customer D          40               40                       0%

               

              I was using the below expression dimensions conditional checkbox which isn't working. Using dimension limi

               

              =if((sum(Cost - Planned))/ sum(Planned) >= 0.10,
                         (
              sum(Cost - Planned))/ sum(Planned),
                    
              if((sum(Cost - Planned))/ sum(Planned) <= -0.10,
                                (
              sum(Cost - Planned))/ sum(Planned)
                     ))

               

              Thanks

                • Re: Display records based on value
                  Michael Andreasson

                  You can put the condition around your expressions:

                   

                  if((sum(Cost - Planned))/ sum(Planned) >= 0.10 OR (sum(Cost - Planned))/ sum(Planned) <= -0.10,

                                    (sum(Cost - Planned))/ sum(Planned)  //Or whatever you want to calculate

                        )

                    • Re: Display records based on value

                      Thanks for that, it works!

                       

                      However the table contains more than just the customer name and variance and it seems that as soon as I start adding the other columns the data is no longer filtered how I'd like.

                      For example I have a cloumn that shows an image (an arrow to show whether the customer is over or under), do you have any ideas on how to apply this rule to this? Below is the code I was using previously.

                       

                       

                      (sum(Cost - Planned)/ sum(Planned)>= 0.101,
                      'qmem://<bundled>/BuiltIn/arrow_n_r.png','qmem://<bundled>/BuiltIn/arrow_s_y.png')

                       

                       

                      I also have a column that uses set analysis, how would I adapt the expression for this? (See code below).

                       

                      =(

                      sum({$<Type ={'AE'}>}Cost) -
                      sum({$<Type ={'AE'}>}Planned))/
                      sum({$<Type ={'AE'}>}Planned

                      )

                       

                      I'm fairly new to Qlikview so any help is much appreciated.

                      Thanks

                • Re: Display records based on value

                  Hi,

                   

                  Try this in the expresion, if(a-b < 10,a-b,0).