topic How to exclude values where the % difference is zero in QlikView

How to exclude values where the % difference is zero

Wed, 08 Jun 2011 10:57:23 GMT

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

Re: How to exclude values where the % difference is zero

Miguel_Angel_Baeyens — Wed, 08 Jun 2011 11:04:39 GMT

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

How to exclude values where the % difference is zero

Wed, 08 Jun 2011 11:08:57 GMT

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.

How to exclude values where the % difference is zero

Wed, 08 Jun 2011 11:09:51 GMT

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.

Re: How to exclude values where the % difference is zero

Miguel_Angel_Baeyens — Wed, 08 Jun 2011 12:30:48 GMT

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

Re: How to exclude values where the % difference is zero

Wed, 08 Jun 2011 12:53:13 GMT

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!