topic Re: 1 Expression 2 Results in QlikView

1 Expression 2 Results

Thu, 20 Mar 2014 10:38:56 GMT

Hi All,

I have a bit of strange one. Using the same expression, I am getting 2 different results when I change dimensions. When I use the most granular dimension,i.e. at customer level, I get the correct result, when I try a different dimension say at region level, I get a different result which is less. I am using the following expression:

If((Sum([Bales Loaded])-Sum([Bales Delivered]))<=0,0,(Sum([Bales Loaded])-Sum([Bales Delivered])))

Is it anything to do with the aggregate function?

Any help appreciated

Herbert

Re: 1 Expression 2 Results

prodanov — Thu, 20 Mar 2014 10:44:33 GMT

The reason for this result is that aggregate functions in condition, are calculated at the level of the dimensions that you have chosen.

Re: 1 Expression 2 Results

CELAMBARASAN — Thu, 20 Mar 2014 10:51:00 GMT

Hi Herbert,

When you goto the granular level it will compute the expression Sum([Bales Loaded]) - Sum([Bales Delivered]) might be different from the on at the high level

Example

Region, SomeID, [Bales Loaded], [Bales Delivered]

NY, 1, 20, 10

NY, 2, 20, 40

when u see at the ID level

NY 1 20-10<=0? gives 10

NY 2 20-40<=0? gives 0

What if on Region level

NY 40 - 50<=0? gives 0 instead of 10 right?

Thats how qlikview will do the operation by default.

Here to achieve yours use Aggr, which might slower but you needed here.

Aggr(If((Sum([Bales Loaded])-Sum([Bales Delivered])), 0, Sum(Sum([Bales Loaded])-Sum([Bales Delivered]))), SomeID, Region)

Then you will get your desired result

Thanks

Celambarsan

Re: 1 Expression 2 Results

Thu, 20 Mar 2014 10:51:04 GMT

Thanks Dimitar,

But how do I fix it? A dimension is basically a different view of the same data set.

Thanks

Re: Re: 1 Expression 2 Results

Thu, 20 Mar 2014 11:32:13 GMT

Thanks Celambarsan, I think its gone a little bit worse actual,

I have attached some dummy data, hope this works, would you mind taking a look.

Thanks

Re: Re: Re: 1 Expression 2 Results

Thu, 20 Mar 2014 12:22:40 GMT

PFA, Hope this will helps you

Re: Re: Re: 1 Expression 2 Results

prodanov — Thu, 20 Mar 2014 12:33:59 GMT

If the condition used functions to check the values of the same level, then any dimension to have the record, you get the same result. It can do this using the functions Aggr and Set Analysis

Re: 1 Expression 2 Results

CELAMBARASAN — Thu, 20 Mar 2014 12:37:22 GMT

I missed to specify Sum around the Aggr

Try the below expression

Sum(Aggr(If((Sum([Bales Loaded])-Sum([Bales Delivered])), 0, Sum(Sum([Bales Loaded])-Sum([Bales Delivered]))), [Customer ID))

Re: 1 Expression 2 Results

Thu, 20 Mar 2014 12:55:37 GMT

Thanks Nirmal,

This is helpful, and making good progress. I am now getting a consistent answer, although its a consistant wrong answer. Does the fact that Loaded Bales and Delivered Bales are coming from 2 different tables matter? I think this might be where the problem is.

Thanks

Re: 1 Expression 2 Results

Thu, 20 Mar 2014 13:11:35 GMT

Hi Herbert

If you got the answer please close the thread.