Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
The reason for this result is that aggregate functions in condition, are calculated at the level of the dimensions that you have chosen.
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
Thanks Dimitar,
But how do I fix it? A dimension is basically a different view of the same data set.
Thanks
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
Hi
PFA, Hope this will helps you
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
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))
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
Hi Herbert
If you got the answer please close the thread.