topic Re: Counting issues in table object in QlikView

Counting issues in table object

Anonymous — Tue, 15 Dec 2015 09:27:38 GMT

Hi all,

I have problem counting when including a field from a dimension table. I have an order number with some lines in my fact table. The order total qty is 200, and the correct total sales amount is 136 240,-. Below is my table object. As you can see, there's a duplicate on order line 50000. That's because this line is connected to two different shipments. If I remove the ShipmentNo field the calculations will be correct.

This is my data model and code (simplified):

Fact:

Load * Inline [

RecNo, OrderNo, OrderLineNo, Shipment_Key, Qty, SalesAmount

1, KO01542142, 10000, 1, 40, 0

2, KO01542142, 30000, 2, 40, 6600

3, KO01542142, 40000, 3, 40, 4000

4, KO01542142, 50000, 4, 40, 6000

5, KO01542142, 80000, 5, 10, 29910

6, KO01542142, 80000, 5, 10, 29910

7, KO01542142, 80000, 5, 10, 29910

8, KO01542142, 80000, 5, 10, 29910

];

Shipment:

Load * Inline [

Shipment_Key, ShipmentNo

1, -

2, LO01665695

3, LO01665633

4, LO01590308

4, LO01672135

5, LO01686726

];

Can I somehow make a calculation in my table object that solves this problem..? Don't wanna change my data model.

3 000,- at each row is good enough. I have tried Sum(SalesAmount) / Count(OrderLineNo) but that will not be correct for line 8000, which has multiple rows in my fact table...

This must be a quite common problem I guess...

Please answer with your suggestions

Best regards

Filip

Re: Counting issues in table object

awhitfield — Tue, 15 Dec 2015 09:39:21 GMT

Hi Filip,

is it correct that you have 2 different shipment numbers for shipment key 4 ?

Andy

Re: Counting issues in table object

MayilVahanan — Tue, 15 Dec 2015 09:42:31 GMT

Try like this

OrderNo	OrderLineNo	ShipmentNo	=SUm(Qty)	=If(RowNo() = 0 or IsNull(RowNo()), Sum(SalesAmount), Sum(SalesAmount) / Count(TOTAL<OrderNo, OrderLineNo> ShipmentNo))
			200	136240
KO01542142	10000	-	40	0
KO01542142	30000	LO01665695	40	6600
KO01542142	40000	LO01665633	40	4000
KO01542142	50000	LO01590308	40	3000
KO01542142	50000	LO01672135	40	3000
KO01542142	80000	LO01686726	40	119640

Re: Counting issues in table object

jonathandienst — Tue, 15 Dec 2015 09:50:35 GMT

Rather than using ShipmentNo as a dimension, use Concat(ShipmentNo, ', ') as an expression,

Re: Counting issues in table object

swuehl — Tue, 15 Dec 2015 09:54:17 GMT

If you use a straight table chart with total mode expression total, I can't reproduce your results:

OrderNo	OrderLineNo	ShipmentNo	Sum(Qty)	Sum(SalesAmount)
			200	136240
KO01542142	10000	-	40	0
KO01542142	30000	LO01665695	40	6600
KO01542142	40000	LO01665633	40	4000
KO01542142	50000	LO01590308	40	6000
KO01542142	50000	LO01672135	40	6000
KO01542142	80000	LO01686726	40	119640

Re: Counting issues in table object

Anonymous — Tue, 15 Dec 2015 13:05:57 GMT

Thank you for this solution, it fits me best. Didn't think in this way..

Best,
Filip