topic Re: Display records based on value in QlikView

Display records based on value

Tue, 24 Jul 2012 11:16:37 GMT

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

Tue, 24 Jul 2012 11:32:36 GMT

post data sample please

Re: Display records based on value

Wed, 25 Jul 2012 12:10:21 GMT

Hi,

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

Re: Display records based on value

Thu, 26 Jul 2012 13:25:32 GMT

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

michael123 — Thu, 26 Jul 2012 14:44:54 GMT

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

Tue, 31 Jul 2012 11:05:15 GMT

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