Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
post data sample please
Hi,
Try this in the expresion, if(a-b < 10,a-b,0).
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
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
)
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