Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Rather than using ShipmentNo as a dimension, use Concat(ShipmentNo, ', ') as an expression,
Hi Filip,
is it correct that you have 2 different shipment numbers for shipment key 4 ?
Andy
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 |
Rather than using ShipmentNo as a dimension, use Concat(ShipmentNo, ', ') as an expression,
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 |
Thank you for this solution, it fits me best. Didn't think in this way..
Best,
Filip