1 Expression 2 Results

[#1]

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

[prodanov]

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

[CELAMBARASAN]

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

[#4]

Thanks Dimitar,

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

Thanks

[#5]

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

[#6 · Accepted solution]

Hi

PFA, Hope this will helps you

[prodanov]

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

[CELAMBARASAN]

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))

[#9]

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

[#10]

Hi Herbert

If you got the answer please close the thread.