WEIRD SUM FUNCTION RESULTS

[#1]

Hi gang

I am trying to add up my usage costs in Qlikview

I have orders: each order has part#s on it. I am trying to add up my total usage by month of each part # and the total cost

I have three expressions.

1. Usage     = OrderShippedQty

2. Average Cost = Avg_Cost

3. TotalCost = Sum(OrderShippedQty*Avg_Cost)

Here's what is weird: the SUM function screws it up.

When I don't put in the Sum function (like this)

I get this - perfect 4,487 units at $1 each is a total cost of $4,487

The SUM function shouldn't change anything - this is right down to the single order level.

But when i put in the SUM function it jumps to $94,227 WHY?

[Anonymous]

Without the Sum() function the expression defaults to Only(), which will only display value if all rows have the same value.

You are getting exactly 23 times than you should with Sum(), which indicates you have 23 rows the same.

What happens if you use the Count() function instead ?

I reckon you'll get a count of 23, as in you have 23 rows the same.

[Qrishna]

Try

Aggr(Sum(OrderShippedQty*Avg_Cost),ProductID)

Usually in your type scenarios where there multiple products and the user is looking for only product's, the summation needs aggregations

would you mind sharing some demo data.?

[#4]

Hi all,

Aggr function should resolve this issue as suggested by krishna,

or you should add in dimension a key filed to differentiate the part# and remove the duplicate 21 rows which give the wrong answer.

[#5]

Perfect! That did it! THank you!

[Qrishna]

Please Close the thread as answered.

Thanks