How to exclude values where the % difference is zero

[#1]

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

[Miguel_Angel_Baeyens]

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.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[#3]

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.

[#4]

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.

[Miguel_Angel_Baeyens · Accepted solution]

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.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[#6]

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!