Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi @ all...
I try to get delivery costs from our table, based on a order. So first of all, i try to describe our Datamodel.
%carrier | deliver_to | cost_per_pack |
DHL | DE | 2,00 |
DHL | NL | 4,00 |
DHL | ES | 9,00 |
... | ... | ... |
UPS | DE | 3,50 |
UPS | NL | 6,90 |
UPS | ES | 12,90 |
... | ... | ... |
Next Table should be our Maintable:
%ordernumber | delivered_country |
123456 | DE |
123457 | NL |
123458 | ES |
... | ... |
123500 | DE |
123501 | NL |
... | ... |
There is one more Table:
%ordernumber | tracking_no | %carrier |
123456 | 456788 | DHL |
123456 | 456789 | DHL |
123456 | 456799 | DHL |
123457 | 456712 | DHL |
123458 | 456735 | DHL |
123500 | 456820 | DHL |
123456 | 1z0815123 | UPS |
123456 | 1z0815139 | UPS |
123501 | 1z0815124 | UPS |
Attention: Tracking count for 123456 = 3 items by DHL and 2 items by UPS ! (split delivery).
I tried to generate a Diagram Table to report the shipping cost by each Ordernumber.
My Dimension of corse: %ordernumber
My calculation shold match with a result like this:
sum({<[%carrier]={'%carrier'}, [deliverd_to]={'delivered_country'}>} shipping_cost) **
In this sample to ordernumber 12346 it would be:
(3x DHL to DE) + (2x UPS to DE) = (3x 2,00) + (2x 3,50) = 13,00 EUR.
** But you already could see - inserting a Dimension, does not work for this calculation.
I have got no Idea how to get this multidimension array into this formular.
Got anyone have a clou?
Regards,
Mirco
I think that's not a use-case for a set analysis which is quite the same as a selection. Therefore I could imagine that approaches like:
count(tracking_no) * avg(cost_per_pack)
and possibly wrapped with an aggr() like:
sum(aggr(count(tracking_no) * avg(cost_per_pack), %ordernumber, %carrier))
might be more suitable.
Important will be in each case how the tables are associated which means that the relevant data-combinations are really available.
Personally I doubt that I would try to calculate it in this way and also separating these information into at least 3 tables else I would map the shipping-costs to each tracking_no - and afterwards a simple sum() would be enough.
I think that's not a use-case for a set analysis which is quite the same as a selection. Therefore I could imagine that approaches like:
count(tracking_no) * avg(cost_per_pack)
and possibly wrapped with an aggr() like:
sum(aggr(count(tracking_no) * avg(cost_per_pack), %ordernumber, %carrier))
might be more suitable.
Important will be in each case how the tables are associated which means that the relevant data-combinations are really available.
Personally I doubt that I would try to calculate it in this way and also separating these information into at least 3 tables else I would map the shipping-costs to each tracking_no - and afterwards a simple sum() would be enough.
This would be easier if you can create a composite key in your data model to map cost based on Carrier and DeliverTo
Thanks for replying my question. @Marcus1 has the closest idea to inspire my thoughts on this task.
It's not realy working, what i was to get with this solution, but we recognised that we are on an absolute wrong way.
In our case, we need to calculate a sum of shippingcosts. This is only in the moment of wrinting this order to our database, the right way. One time a year - we get new shipping rates. So in fact - after some years, we need the archieved shipping coast - not the current.
So we decide to resolve this in our ERP System. Our order-database will get a new fiel "payed_shippingcosts" on an other step in our workflow. At least - i just get read this field for my analytics.
Thanks for your suggestions.