topic Re: WEIRD SUM FUNCTION RESULTS in QlikView

WEIRD SUM FUNCTION RESULTS

Tue, 13 Oct 2015 17:25:05 GMT

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 — Tue, 13 Oct 2015 17:32:06 GMT

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 — Tue, 13 Oct 2015 17:50:30 GMT

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.?

Wed, 14 Oct 2015 07:40:18 GMT

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.

Thu, 15 Oct 2015 15:41:39 GMT

Perfect! That did it! THank you!

Qrishna — Thu, 15 Oct 2015 16:24:45 GMT

Please Close the thread as answered.

Thanks