Counting issues in table object

[Anonymous]

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

[awhitfield]

Hi Filip,

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

Andy

[MayilVahanan]

Hi

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

[jonathandienst · Accepted solution]

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

[swuehl]

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

[Anonymous]

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

Best,
Filip