Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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.

5 Replies
Not applicable
Author

post data sample please

Not applicable
Author

Hi,

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

Not applicable
Author

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

michael123
Partner - Creator
Partner - Creator

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

      )

Not applicable
Author

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