Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I'm a newbie so please bear with me ( I have searched to see if I could find an asnwer to this from the existing discussions ). I have a table where I calcaulate the % differences between two fields using a simple expression. What I'd like to do though is use either an expression, or setting in the table box that means ONLY those rows where the % difference between Field A and Field B is > 0%.
It's for a reconciliation of 500,000 records where I only want to see those that don't match!
Many thanks.
Tim
Hi,
Assuming the following data
Data:
LOAD * INLINE [
ID, FieldA, FieldB
A, 1000, 1200
B, 200, 190
C, 1000, 1000
D, 400, 230
];
I create a new chart, straight table, use ID, FieldA and FieldB as dimensions and the following as expression
Sum({< ID = {"=(FieldA - FieldB) > 0"} >} FieldA / FieldB)
This will return only two values (B and D).
I'm not replicating the logic of your data, of course, but you can see how it works.
Hope this helps.
BI Consultant
Hello Tim and welcome to QlikView and the Community,
There are several ways to do that, and I'm assuming you are doing the calculation in a chart. If that's the case, then try with the following expression and adapt it to your own fields:
Sum({< FieldA = {'>0'}, FieldB = {'>0'} >} Amount)
That will return the aggregation of Amount per value in the dimension where FieldA and FieldB are greater than zero.
Hope that helps.
BI Consultant
Thanks Miguel, actually I need to screen the % result rather than the field A & B values. For example in the result set below I'd only want to see the middle row;
A B % diff
55 55 0%
56 55 2%
45 45 0%
I'm diplaying the results in a simple table. Any ideas?
Thanks again.
Thanks Miguel, actually I need to screen the % result rather than the field A & B values. For example in the result set below I'd only want to see the middle row;
A B % diff
55 55 0%
56 55 2%
45 45 0%
I'm diplaying the results in a simple table. Any ideas?
Thanks again.
Hi,
Assuming the following data
Data:
LOAD * INLINE [
ID, FieldA, FieldB
A, 1000, 1200
B, 200, 190
C, 1000, 1000
D, 400, 230
];
I create a new chart, straight table, use ID, FieldA and FieldB as dimensions and the following as expression
Sum({< ID = {"=(FieldA - FieldB) > 0"} >} FieldA / FieldB)
This will return only two values (B and D).
I'm not replicating the logic of your data, of course, but you can see how it works.
Hope this helps.
BI Consultant
Aha, I see, like the logic, exclude rows where the Field values 1 & 2 are equal in the first part of hte expression then only generate the % result for those where there's a difference. 🙂
Thanks!