topic Re: Cancell Doubling using sum in QlikView

Cancell Doubling using sum

Wed, 25 Nov 2020 16:16:04 GMT

Hey everyone,

When I try to do sum(average*cost) it's doubling by the number of the number of the warehouses.

I tried to divide by the number of the warehouses [sum(average*cost)/count(warehouses)] but then I have a problem in the total.

Cant figure out how to solve this

Regards,

Maayan

sunny_talwar — Thu, 12 May 2016 16:13:26 GMT

I am not sure I understand the number you are looking to get? May be if you can share a sample with expected output, one of us can help you out.

Thu, 12 May 2016 17:11:34 GMT

I looking for way to double the "avg use" with the cost and finally see the total.

Now when I try to do this with sum it's double with the number of the warehouses.

In the image you can see that the average*cost is 69151 and it need to be 34575 (half).

This happens because that the serial is in 2 warehouses

Now it is more understandable?

idogridish2 — Thu, 12 May 2016 17:21:37 GMT

Hi Maayan,

ma shlomeh?

maybe you can try avg([use average])*avg(cost)

hope it helped.

swuehl — Thu, 12 May 2016 17:28:20 GMT

Or maybe

=Sum(Aggr( sum(average*cost) / count(warehouses), Serial))

edit:

Or maybe using a different outer aggregation...

I assume your issue is caused by the way your data model is built, but that's hard to tell without knowing it.