Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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

Re: Display records based on value

post data sample please

Not applicable

Re: Display records based on value

Hi,

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

Not applicable

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

michael123
Contributor

Re: Display records based on value

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

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

Community Browser